How to update multiple columns in single update statement in DB2
If the values came from another table, you might want to use
UPDATE table1 t1
SET (col1, col2) = (
SELECT col3, col4
FROM table2 t2
WHERE t1.col8=t2.col9
)
Example:
UPDATE table1
SET (col1, col2, col3) =(
(SELECT MIN (ship_charge), MAX (ship_charge) FROM orders),
'07/01/2007'
)
WHERE col4 = 1001;
The update statement in all versions of SQL looks like:
update table
set col1 = expr1,
col2 = expr2,
. . .
coln = exprn
where some condition
So, the answer is that you separate the assignments using commas and don't repeat the set
statement.