如何在DB2中更新单个update语句中的多个列?

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

我想用一个update语句更新DB2中一个表的多个列。

Any hint or idea will be appreciable. Thanks.

任何暗示或想法都是值得欣赏的。谢谢。

5 个解决方案

#1

The update statement in all versions of SQL looks like:

SQL的所有版本中的update语句如下:

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.

所以，答案是你用逗号分开作业，不要重复设置语句。

#2

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;

#3

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:

不是标准SQL，也不工作你要用这个，就像Gordon Linoff说的:

update table
    set col1 = expr1,
        col2 = expr2,
        . . .
        coln = exprn
    where some condition

#4

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:

我知道这是一个老问题，但我必须找到多行更新的解决方案，其中多个记录必须根据它们的id更新不同的值，我发现我可以使用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:

另外，我发现在这个更新中指定不使用NULL值是非常重要的(以防第一个表中的所有记录在第二个表中都有相应的记录)，这样:

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

来源:https://www.ibm.com/support/knowledgecenter/ssw_i5_54/sqlp/rbafyupdatesub.htm

#5

This is an "old school solution", when MERGE command does not work (I think before version 10).

这是一个“旧学校解决方案”，当MERGE命令不起作用时(我认为在版本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));

#1