SQL db2：使用不同表上的选择更新多行和字段

db2: update multiple rows and field with a select on a different table

提问by Luka

is it possible to increment the field a and b of a table (A.a and A.b) using the value c and d of a different table (B.c B.d) for all the row of A where A.x == B.z?

是否可以使用不同表（Bc Bd）的值 c 和 d 为 Ax == Bz 的所有行增加表（Aa 和 Ab）的字段 a 和 b？

I'm getting crazy with this query

我对这个查询很着迷

采纳答案by aF.

DB2 and the SQL standard don't have a FROM clause in an UPDATE statement. So you have to clearly separate the steps to

DB2 和 SQL 标准在 UPDATE 语句中没有 FROM 子句。所以你必须清楚地分开步骤

1. identify the rows to be modified and to
2. compute the new value.

1. 确定要修改的行并
2. 计算新值。

.

.

Here is an example:

下面是一个例子：

UPDATE TABLE A
SET A.FLD_SUPV = ( SELECT B.FLD_SUPV
FROM TABLEA A, TABLEB B, TABLEC C,TABLED D
WHERE A.FLD1= B.FLD1
AND A.FLD_DT >= B.FLD_FM_DT
AND A.FLD_DT <= B.FLD_THRU_DT
AND A.FLD_DT > D.FLD_THRU_DT
AND A.FLD_DT < C.FLD_EFF_DT )
WHERE EXISTS ( SELECT B.FLD_SUPV
FROM TABLEA A, TABLEB B, TABLEC C,TABLED D
WHERE A.FLD1= B.FLD1
AND A.FLD_DT >= B.FLD_FM_DT
AND A.FLD_DT <= B.FLD_THRU_DT
AND A.FLD_DT > D.FLD_THRU_DT
AND A.FLD_DT < C.FLD_EFF_DT )

To update two fields you may use an example like this:

要更新两个字段，您可以使用这样的示例：

UPDATE table1 t1 
 SET (col1, col2) = (
  SELECT col3, col4 
  FROM  table2 t2 
  WHERE t1.col8=t2.col9
 )

The optimizer will see that the sub-queries in the SET and the FROM clause are identical and it should merge them in the internal execution plan.

优化器将看到 SET 和 FROM 子句中的子查询是相同的，它应该将它们合并到内部执行计划中。

回答by jhnwsk

Yes it is possible. You can try something like this:

对的，这是可能的。你可以尝试这样的事情：

MERGE INTO A
USING (SELECT c, d, z from B) B
ON (A.x = B.z)
WHEN MATCHED THEN
UPDATE SET A.a = A.a + B.c, A.b = A.b + B.d;

You can read more about MERGE here.

您可以在此处阅读有关 MERGE 的更多信息。

回答by Tim Seed

Tested under DB2 10.6

在 DB2 10.6 下测试

Practically the same as @jhnwsk, but with added table short cuts.

实际上与@jhnwsk 相同，但添加了表格快捷方式。

Merge into "PRODUCTION"."COUNTRY" as N
      using (SELECT OD.NAME,OD.KEY from "DEVELOPMENT"."COUNTRY" as OD) as O
      on (O.KEY = N.KEY)
      WHEN MATCHED THEN
           UPDATE SET N.NAME=O.NAME;

回答by Vladislav Troyan

This one also works quite good

这个也很好用

update TableA A set a = (select a from TableB B where A.x = B.z) where exists (select 1 from TableB B where A.x = B.z) ;

update TableA A set a = (select a from TableB B where A.x = B.z) where exists (select 1 from TableB B where A.x = B.z) ;

回答by FELIPE

UPDATE CS70P t1 
 SET (T1.TIPOCRE) = (
  SELECT MOROSO
  FROM  FCSALDOC t2 ,CS70P t1
  WHERE t1.NUMCRE =t2.CODCTA
 )