Update several columns at once in Derby

Question

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)?

Answer 1

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.