Jason Bosco

Full Stack Web Developer ; Generalist

MySQL: Updating Multiple Columns When Using Select

I’ve always wanted to do something like this:

UPDATE table1 SET (col1,col2)=(SELECT x,y FROM table2 WHERE table1. CommonColumn =table2.CommonColumn);

Finally I got around to looking it up and found the answer to how to do it on SQL Server:

UPDATE
    table1
SET
    table1.col1 = table2.x,
    table1.col2 = table2.y
FROM
    table1
INNER JOIN
    table2
ON
    table1.CommonColumn = table2.CommonColumn

However, in MySQL this is not supported. To achieve the same effect in MySQL I came up with this:

UPDATE
    table1 INNER JOIN table2 USING (CommonColumn)
SET
    table1.col1 = table2.x,
    table1.col2 = table2.y

The UPDATE syntax for MySQL is here:

http://dev.mysql.com/doc/refman/5.6/en/update.html