code sauce

...

Mysql: Updating Multiple Columns With Select

| Comments

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 :

1
2
3
4
5
6
7
8
9
10
11
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(). so the whole thing becomes something like

1
2
3
4
5
UPDATE
    table1 INNER JOIN table2 USING (CommonColumn)
SET
    table1.col1 = table2.x,
    table1.col2 = table2.y

Comments