I'm trying to update table a with data from some of the columns in table b. Column names are matching in both tables, cannot figure out the syntax, can anyone help?
This is what I want to do (expressed out-of-syntax):
UPDATE table_a SET table_a.col1 = table_b.col1, table_a.col2 = table_b.col2 WHERE table_a.id = table_b.id
Maybe (probably) I would need some kind of JOIN-clause, but I haven't gotten my head around those yet.... :-/
CodePudding user response:
You can update your table using a JOIN of the two tables:
UPDATE table_a a
INNER JOIN table_b b ON a.id = b.id
SET a.col1 = b.col1, a.col2 = b.col2
I see you are on MYSQL. Not sure if the version above works. If not, try:
UPDATE table_a a
INNER JOIN table_b b
SET a.col1 = b.col1, a.col2 = b.col2
WHERE a.id = b.id
CodePudding user response:
Ok, so I found it myself... :-)
MERGE (without using the WHEN NOT MATCHED clause)is the answer to my problem.
My solution:
MERGE INTO table_a USING table_b ON a.id=b.id WHEN MATCHED THEN UPDATE SET col1 = b.col1, col2 = b.col2;