How to update multiple columns in single update statement in DB2

Question

I want to update multiple columns of a table in DB2 with single Update statement.

Any hint or idea will be appreciable. Thanks.

score 62 · Accepted Answer · answered Mar 26 '14 at 12:12

62

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.

answered Mar 26 '14 at 12:12

Gordon Linoff

1,242,037
58
646
786

5

DB2 also supports row assignments: `update table set (col1, col2, ...) = (expr1, expr2, ...) where... ` – mustaccio Mar 26 '14 at 12:27
@mustaccio I tried your version and also the above mentioned by Gordon but they didn't work. Then I asked question here. The thing which is different in my query is this... update (select specific record of the table based on some condition) set col1 = val1, col2 = val2; update (select specific record of the table based on some condition) set (col1, col2) = (val1, val2); – Superman Mar 26 '14 at 12:52
@Superman . . . Ask another question, this time provide detail on what you are actually trying to do. Just editing this question is unfair to anyone who has answered the original version (namely, me). – Gordon Linoff Mar 26 '14 at 13:36
3

@Superman . . . It is just a general principle that when the question is substantially changed, it affects people who have already tried to solve it, either through answers or comments. Better to delete the question and start over again. (And I don't need the points from this question.) – Gordon Linoff Mar 26 '14 at 15:12

score 12 · Answer 2 · edited Jul 11 '17 at 13:21

12

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;

edited Jul 11 '17 at 13:21

AdrienTorris

9,111
9
34
52

answered Nov 13 '16 at 20:38

betrice mpalanzi

1,436
12
16

Hennie van Dyk · Answer 3 · 2018-01-09T15:18:03.580

2

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

edited Jan 09 '18 at 15:18

answered Jan 08 '18 at 16:33

Hennie van Dyk

51
2

score 1 · Answer 4 · edited Jul 21 '17 at 20:36

1

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

edited Jul 21 '17 at 20:36

garfbradaz

3,424
7
43
70

answered Jul 21 '17 at 19:52

Dapper Dan

932
11
23

score 1 · Answer 5 · answered Jan 08 '20 at 14:10

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

score 0 · Answer 6 · edited Oct 19 '17 at 14:28

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

How to update multiple columns in single update statement in DB2

6 Answers6