Multicolumn UPDATE with Subquery the MySQL way

We all know the usual UPDATE statement, for example:
UPDATE table_1 SET column_a1 = 57;
And if we want to update more than 1 column, we do this:
UPDATE table_1 SET column_a1 = 57, column_a2 = 37;
Or, assuming we are using something else than MySQL, we can do this:
UPDATE table_1 SET (column_a1, column_a2) = (57, 37);
The last two statements do the same thing, but using slightly different syntaxes (is that a proper word?), both in the SQL standard, and both supported by most RDBMSs, but only the former one by MySQL. Which really doesn't matter much, as these are just two ways to express the same thing, and the two ways aren't really that different.

An UPDATE statement with a subquery, where you use the subquery to fetch the information for the new values for the updated columns is not an uncommon operation. It can look like this, for example:
UPDATE table_a SET column_a1 = (SELECT column_b1 FROM table_b
WHERE table_b.column_b3 = table_a.column_a3);

Nothing special with this, this is the way MySQL does it, and this is how the SQL standard says it should be done. When looking at this syntax and comparing it with an UPDATE without a subquery, you realize that if you are to update 2 columns in table_a, from 2 columns in table_b you would do this:
UPDATE table_a SET column_a1 = (SELECT column_b1 FROM table_b
WHERE table_b.column_b3 = table_a.column_a3),
column_a2 = SELECT column_b2 FROM table_b
WHERE table_b.column_b3 = table_a.column_a3);

Now, looking at this, one realizes that in most cases when this syntax is used, the condition in the two subqueries, will almost always be the same, we are just getting two different columns from table_b. The SQL standard allows you to use a constriction with a ROW value in these cases, and it looks like this:
UPDATE table_a SET (column_a1, column_a2) = (SELECT
column_b1, column_b2 FROM table_b
WHERE table_b.column_b3 = table_a.column_a3);

This will achieve the same result as the second of the SQL statements above, but is less error prone (I one have to specify the query once) and it makes it easy to understand the intention of the query, as well as for the optimizer to do it's job better, oh my, what a blessing. Except that this later construct, supported by most SQL databases, and part of the SQL standard, is not supported by MySQL. Yikes!

Which doesn't mean that you cannot DO this with MySQL. Not at all, but the MySQL syntax is a bit different, and is an extension of the SQL syntax. If you are used to some other RDBMS than MySQL, then you might not know about the MySQL specific multi table UPDATE and DELETE statements. Here we will look at how the former can be used to achieve what we did with standard SQL above:
UPDATE table_a JOIN table_b ON table_a.column_a3 = table_b.column_b3
SET table_a.column_a1 = table_b.column_b1,
table_a.column_a2 = table_b.column_b2;

In a sense, this makes some sense (sic!). The table join is an ANSI join, which is allowed here but confusingly enough, not with multi-table. But the fact remains, this is not standard SQL. But it is less error prone than the variation above with two subqueries, which IS standard SQL, and is probably faster also, unless the MySQL subquery optimizer does something very smart here.

But all in all, I'd prefer that MySQL supported the standard "row constructor" syntax (this is the term for the ( value [, value] ) syntax in general) for four reasons:

• It IS the SQL standard way of doing things.
• It is supported by most other RDBMSs.
• It is just a syntactic thing (as, as can be seen above, the same can be achieved with a slightly different syntax in MySQL).
• It is easier to see what is happening, in this case. ANSI JOINs are nice, but in this case, I think they blur the subject of what we are trying to achieve.

And to finish up, no I do not think that MySQL should NOT support the multi-table UPDATE syntax, this is a useful addition to the standard SQL syntax, but in this case, the standard is better (and it is standard), but the multi-table UPDATE can do more things that you cannot do with the "row constructor" UPDATE syntax. Maybe I should do a worklog for "row constructor" style UPDATE support for MySQL...

/Karlsson