UPDATE table1SET col1 = t2.col1, col2 = t2.col2FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id
UPDATE table1 t1 JOIN table2 t2 ON t1.id = t2.idSET t1.col1 = t2.col2, t1.col2 = t2.col2
Thanks. This is the only place that I found a clear answer.
lolYou're welcome.Now you know how I felt. ;-)
Are the table aliases necessary?
No. 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.As 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
clarity now, gratitude laterThanks!
"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. One 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.idSET t1.col1 = t2.col2, t2.col2 = t2.col2Thank you!!
You're right, if the update columns have same name, you're going to get an error. My bad.It 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.UPDATE 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:UPDATE orders AS OLEFT JOIN pgs_accounts_2 AS A ON A.customers_ID=O.customers_ID LEFT JOIN countries AS C ON C.countries_ID=A.countries_IDSET 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 ;)On 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!!
I hate mysql.
Ditto. It's weak, but it does have a lot of nice functions.
Thank you a lot.
you're a genius. thx!
searching....searching...searching...finally found what I was looking forTHANKS!
Many Thanks! This was helpful.
Thanks, just what I was looking for.
Now I can update memberinfo for my forum with a SQL line!Thanks! 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;PSL contains 14,8 Millions Rows... Win!
Thanks, very useful and clear answer.
Sorry I tried it and it didn't work.