Update multiple columns in Derby at once

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 select, which returns more than one column, and copy the results to the different columns of the destination table (the one to be updated).

Derby only allows syntax:

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

which means that I can run into problems when I need to somehow group the results of the selection. Is there a better solution than splitting an update into two statements or doing it locally in a loop in Java (i.e., representing 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)

- , , , , .