UPDATE syntax that works in major databases

Question

I want to update a table (target) based on the values present in another table (source). But I am looking for a syntax that will work in 4 major databases - Oracle, MS SQL Server, PostgreSQL, MySQL.

So far, I am not able to find such a uniform syntax. Have I missed such syntax or there is really no such syntax?

Oracle

UPDATE target t
SET (t.col1, t.col2) = (SELECT s.col1, s.col2 
                       FROM source s 
                       WHERE s.key = t.key)

MS SQL Server / PostgreSQL

UPDATE target t
SET t.col1 = s.col1, t.col2 = s.col2
FROM source s
WHERE t.key=s.key

MySQL

UPDATE target, source 
SET t.col1=s.col1, t.col2=s.col2
WHERE s.key=t.key

Answer 1

It is inefficient, but the ANSI SQL Standard way to do this is:

UPDATE target
SET col1 = (SELECT s.col1
           FROM source s 
           WHERE s.key = target.key),
    col2 = (SELECT s.col2 
           FROM source s 
           WHERE s.key = target.key);

This does not mean to say it will work in every RDBMS (e.g. I don't think it would work in Access), but it does work in the 4 you have listed.

I would personally value performance over portability every day of the week so I would not use this syntax. I would be inclined to use a use a stored-procedure, with a common name, but differing syntax for each RDBMS.

UPDATE

Actually, the method you have shown for Oracle using row value constructors is also allowed by the ANSI SQL Standard:

UPDATE target
SET (t.col1, t.col2) = (SELECT s.col1, s.col2 
                       FROM source s 
                       WHERE s.key = t.key);

Unfortunately, as mentioned above, just because it is in the ANSI Standards it does not mean that it works across platforms.