SQL 如何在 DB2 中的单个更新语句中更新多个列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22660608/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update multiple columns in single update statement in DB2
提问by Superman
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.
任何提示或想法将是可观的。谢谢。
回答by Gordon Linoff
The update statement in all versions of SQL looks like:
所有版本的 SQL 中的更新语句如下所示：
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 setstatement.
因此，答案是您使用逗号分隔作业并且不要重复该set语句。
回答by betrice mpalanzi
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;
回答by Dapper Dan
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 Linoffsaid:
不是标准 SQL 并且不起作用，你必须使用它，正如Gordon Linoff所说：
update table
set col1 = expr1,
col2 = expr2,
. . .
coln = exprn
where some condition
回答by Hennie van Dyk
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));
回答by Fernando Silva
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
来源：rpgpgm.com
SQL only
仅 SQL
UPDATE t1 SET ROW = (SELECT *
FROM t2
WHERE t2.c3 = t1.c3)
Source: ibm.com
来源：ibm.com
回答by InitK
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 使用不同的值进行更新，我发现我可以使用 aa scalar-subselect：
UPDATE PROJECT
SET DEPTNO =
(SELECT WORKDEPT FROM EMPLOYEE
WHERE PROJECT.RESPEMP = EMPLOYEE.EMPNO)
WHERE RESPEMP='000030'
(with WHERE optional, of course)
（当然，WHERE 是可选的）
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
