T-SQL:
UPDATE table1
SET col1 = t2.col1, col2 = t2.col2
FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id
MySQL:
UPDATE table1 t1 JOIN table2 t2 ON t1.id = t2.id
SET t1.col1 = t2.col2, t1.col2 = t2.col2
Friday, March 28, 2008
MySQL - UPDATE with JOIN
The syntax for cross-table update in MySQL is somewhat different than T-SQL. Personally, the way it's done in MySQL makes more sense.
T-SQL:
MySQL:
T-SQL:
MySQL:
Subscribe to:
Post Comments (Atom)
Thanks. This is the only place that I found a clear answer.
ReplyDeletelol
ReplyDeleteYou're welcome.
Now you know how I felt. ;-)
Are the table aliases necessary?
ReplyDeleteNo. However, it's good practice to always specify which table a column belongs to when you're using more than one table in a query. Even if you don't have identical columns now, you may end up adding one in the future and it'll kill the query. It also helps to see which column is from where.
ReplyDeleteAs far as aliases go, when using someone else's db, or even my own, using full table names instead of aliases makes the code a lot less readable, not to mention a lot more typing.
Thanks! This was helpful. Just what I needed to do
ReplyDeleteclarity now, gratitude later
ReplyDeleteThanks!
"Insanity now, serenity later" captures the mantra I operated under while spending X hours searching for functional syntax to update several just created, still empty columns in my table a, using several columns from table b. For the longest time, until I found this post, the suggested solutions were just plain wrong, or insisted on illustrating joins using the "select" clause.
ReplyDeleteOne comment: To get the above mysql syntax to work for me, I had to "t1" before col1 and col2 after SET:
UPDATE table1 t1 JOIN table2 t2 ON t1.id = t2.id
SET t1.col1 = t2.col2, t2.col2 = t2.col2
Thank you!!
Perfect. Thanks.
ReplyDeleteYou're right, if the update columns have same name, you're going to get an error. My bad.
ReplyDeleteIt really annoys me that a lot of the online resources, instead of telling you the answer, pad the page with lines of irrelevant code and extensive explanations.
If you need to do an update on a join, with a where exclusion clause, here's a sample that might help.
ReplyDeleteUPDATE table1 t1
JOIN table2 t2 ON t1.id=t2.id
SET t1.col1=[some value]
WHERE t2.col2 = 1 AND t1.col1 IS NULL;
Does anyone know what the syntax is when 3 tables are involved? This does not work:
ReplyDeleteUPDATE orders AS O
LEFT JOIN pgs_accounts_2 AS A
ON A.customers_ID=O.customers_ID
LEFT JOIN countries AS C
ON C.countries_ID=A.countries_ID
SET 0.customers_country=C.countries_name;
This is the error message:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0.customers_country=C.countries_name' at line 6
The above joins work in a SELECT statement. I had thought that maybe a comma was missing before the second LEFT JOIN, but that did not fix the problem.
Any help would be appreciated.
Typo ;)
ReplyDeleteOn last line, you have the number 0 instead of letter O.
Thanks Moshe, I was just coming back here to post an update about it being a typo. Too bad the error message didn't say there was an unrecognized alias. Duh!!
ReplyDeleteI hate mysql.
ReplyDeleteDitto. It's weak, but it does have a lot of nice functions.
ReplyDeleteThank you a lot.
ReplyDeleteyou're a genius. thx!
ReplyDeleteNice one.
ReplyDeletesearching....searching...searching...
ReplyDeletefinally found what I was looking for
THANKS!
Many Thanks! This was helpful.
ReplyDeleteThanks, just what I was looking for.
ReplyDeleteNow I can update memberinfo for my forum with a SQL line!
ReplyDeleteThanks! This was helpful.
UPDATE play_session_log AS psl LEFT JOIN play_session AS ps ON ps.`id`=psl.`play_session_id` SET psl.customer_id=ps.customer_id;
ReplyDeletePSL contains 14,8 Millions Rows... Win!
Thanks, very useful and clear answer.
ReplyDeleteSorry I tried it and it didn't work.
ReplyDeleteThanks!
ReplyDelete