mysql: updating multiple columns with select

Posted by gjergj.sheldija on March 9, 2013

sometimes even the simples of the things becomes..hard.
one of those things is updating multimple columns in mysql.
the standard should be something as easy as :

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

instead mysql has a quirk that can be used, USING()</a>. so the whole thing becomes something like

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