db2：使用另一个表上的select更新多个行和字段 [英] db2: update multiple rows and field with a select on a different table

问题描述

是否有可能针对Ax的所有行（其中Ax == Bz）使用不同表（Bc Bd）的值c和d来增加表（Aa和Ab）的字段a和b？ >

这个查询让我发疯了

解决方案

DB2和SQL标准UPDATE语句中没有FROM子句。
因此，您必须清楚地将步骤分开

1. 标识要修改的行并


2. 计算新值。

。

示例：

 更新表A 
 SET A.FLD_SUPV =（从TABLEA中选择B.FLD_SUPV 
 A，TABLEB B，TABLEC C，TABLED D 
其中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）
存在（选择B.FLD_SUPV 
来自TABLEA A，TABLEB B，TABLEC C，平板电脑D 
其中A.FLD1 = B.FLD1 
并且A.FLD_DT> = B.FLD_FM_DT 
 AND A.FLD_DT< = B.FLD_THRU_DT 
 AND A .FLD_DT> D.FLD_THRU_DT 
和A.FLD_DT< C.FLD_EFF_DT）
  

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

  UPDATE table1 t1 
 SET（col1，col2）= （
 SELECT col3，col4 
 FROM表2 t2 
 t1.col8 = t2.col9 
）
  

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

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?

I'm getting crazy with this query

解决方案

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

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

.

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.

这篇关于db2：使用另一个表上的select更新多个行和字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！