I want to update multiple columns of a table in DB2 with single Update statement.
Any hint or idea will be appreciable. Thanks.
This question is related to
sql
database
db2
sql-update
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;
update table_name set (col1,col2,col3) values(col1,col2,col);
Is not standard SQL and not working you got to use this as Gordon Linoff said:
update table
set col1 = expr1,
col2 = expr2,
. . .
coln = exprn
where some condition
This is an "old school solution", when MERGE command does not work (I think before version 10).
UPDATE TARGET_TABLE T
SET (T.VAL1, T.VAL2 ) =
(SELECT S.VAL1, S.VAL2
FROM SOURCE_TABLE S
WHERE T.KEY1 = S.KEY1 AND T.KEY2 = S.KEY2)
WHERE EXISTS
(SELECT 1
FROM SOURCE_TABLE S
WHERE T.KEY1 = S.KEY1 AND T.KEY2 = S.KEY2
AND (T.VAL1 <> S.VAL1 OR T.VAL2 <> S.VAL2));
I know it's an old question, but I just had to find solution for multiple rows update where multiple records had to updated with different values based on their IDs and I found that I can use a a scalar-subselect:
UPDATE PROJECT
SET DEPTNO =
(SELECT WORKDEPT FROM EMPLOYEE
WHERE PROJECT.RESPEMP = EMPLOYEE.EMPNO)
WHERE RESPEMP='000030'
(with WHERE optional, of course)
Also, I found that it is critical to specify that no NULL values would not be used in this update (in case not all records in first table have corresponding record in the second one), this way:
UPDATE PROJECT
SET DEPTNO =
(SELECT WORKDEPT FROM EMPLOYEE
WHERE PROJECT.RESPEMP = EMPLOYEE.EMPNO)
WHERE RESPEMP IN (SELECT EMPNO FROM EMPLOYEE)
Source: https://www.ibm.com/support/knowledgecenter/ssw_i5_54/sqlp/rbafyupdatesub.htm
For the sake of completeness and the edge case of wanting to update all columns of a row, you can do the following, but consider that the number and types of the fields must match.
Using a data structure
exec sql UPDATE TESTFILE
SET ROW = :DataDs
WHERE CURRENT OF CURSOR; //If using a cursor for update
Source: rpgpgm.com
SQL only
UPDATE t1 SET ROW = (SELECT *
FROM t2
WHERE t2.c3 = t1.c3)
Source: ibm.com
Source: Stackoverflow.com