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.

ANSI update syntax (it should work in DBMS that you listed):

update t1 
    set col1 = (
                select col1 
                from t2 
                where t1.key = t2.key
              ),
    set col2 = (
                select col2 
                from t2 
                where t1.key = t2.key
              )