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

User · Answer

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

User · Answer

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  lt  gt  S VAL1 OR T VAL2  lt  gt  S VAL2

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] How to update multiple columns in single update statement in DB2

Examples related to sql

Examples related to database

Examples related to db2

Examples related to sql-update