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:
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

26 comments:

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

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

    ReplyDelete
  3. Are the table aliases necessary?

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

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

    ReplyDelete
  6. clarity now, gratitude later
    Thanks!

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

    Thank you!!

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

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

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

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

    ReplyDelete
  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!!

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

    ReplyDelete
  14. you're a genius. thx!

    ReplyDelete
  15. searching....searching...searching...

    finally found what I was looking for

    THANKS!

    ReplyDelete
  16. Many Thanks! This was helpful.

    ReplyDelete
  17. Thanks, just what I was looking for.

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

    ReplyDelete
  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!

    ReplyDelete
  20. Thanks, very useful and clear answer.

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

    ReplyDelete