Friday, March 28, 2008


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.
UPDATE table1
SET col1 = t2.col1, col2 = t2.col2
FROM table1 t1 JOIN table2 t2 ON =

UPDATE table1 t1 JOIN table2 t2 ON =
SET t1.col1 = t2.col2, t1.col2 = t2.col2


  1. Thanks. This is the only place that I found a clear answer.

  2. lol
    You're welcome.
    Now you know how I felt. ;-)

  3. Are the table aliases necessary?

  4. 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.

  5. Thanks! This was helpful. Just what I needed to do

  6. clarity now, gratitude later

  7. "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 =
    SET t1.col1 = t2.col2, t2.col2 = t2.col2

    Thank you!!

  8. 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.

  9. 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
    SET t1.col1=[some value]
    WHERE t2.col2 = 1 AND t1.col1 IS NULL;

  10. Does anyone know what the syntax is when 3 tables are involved? This does not work:

    UPDATE 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.

  11. Typo ;)
    On last line, you have the number 0 instead of letter O.

  12. 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!!

  13. Ditto. It's weak, but it does have a lot of nice functions.

  14. you're a genius. thx!

  15. searching....searching...searching...

    finally found what I was looking for


  16. Many Thanks! This was helpful.

  17. Thanks, just what I was looking for.

  18. Now I can update memberinfo for my forum with a SQL line!
    Thanks! This was helpful.

  19. 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!

  20. Thanks, very useful and clear answer.

  21. Sorry I tried it and it didn't work.