Update several columns at once in Derby

DB2 supports this syntax:

UPDATE DEST D SET (AAA,BBB) = (
    SELECT MAX(Z.AAA), MAX(Z.BBB) FROM OTHER O WHERE O.ID = D.ID
)

i.e. I can run a select which returns more than one column and copy the results into various columns of the destination table (the one to update).

Derby only allows the syntax:

UPDATE table-Name [[AS] correlation-Name]
    SET column-Name = Value
    [ , column-Name = Value} ]*
    [WHERE clause]

which means I can run into problems when I need to group the results of the select in some way. Is there a better solution than splitting the update into two statements or doing this locally in a loop in Java (i.e. submitting millions of UPDATE statements)?

From stackoverflow

• Presumably, you can do this:

  UPDATE DEST D
      SET AAA = (SELECT MAX(Z.AAA) FROM OTHER O WHERE O.ID = D.ID),
          BBB = (SELECT MAX(Z.BBB) FROM OTHER O WHERE O.ID = D.ID)
  

  I didn't say anything about efficient - but it is likely more efficient than either splitting the update into two statements or doing it locally in a loop.

  Aaron Digulla : I'm wary of the side effects; is it possible that the two statements can yield different results when the select clause is more complex?

  Jonathan Leffler : It shouldn't since the table you're selecting from is not updated. I'd be willing to argue bug, I think, if that was a problem. You also have a feature request to make, of course - the multiple-assignment version should work, I think.

  Jonathan Leffler : There's also a modest chance that the optimizer spots the commonality and effectively does the sub-queries as one operation.