具有连接查询的DB2更新 [英] DB2 Update with join queries

问题描述

我正在使用DB2执行以下更新操作。

 更新DATA设置B_DESC = P_DESC，P_DESC = null 
其中B_DESC = *，P_DESC = * 
（从C_DATA中选择B_DESC，P_DESC）
  

以下实际上是可能的，但是由于该子查询中涉及到复杂的连接，所以不建议使用如下

 更新数据设置B_DESC = P_DESC，P_DESC = null 
其中B_DESC =（从C_DATA选择B_DESC），P_DESC =（从C_DATA选择P_DESC）
  

我必须更新DATA表，但是B_DESC和P_DESC我必须从C_DATA表中获取它，并在UPDATE查询中使用它。

<请让我知道如何做。如果可能，它必须是一个单一的查询。

提前感谢

解决方案>

使用合并查询更新表而不是连接。 DB2不接受为此更新查询的加入，您必须使用合并：

 合并到TABLE_NAME1 A 
 USING（SELECT COL1，COL2 FROM TABLE_NAME2）B 
 ON A.COL1 = B.COL2 
 WHEN MATCHED和A.COL1 = B.COL2 
 THEN UPDATE SET A.COL1 = B. COL2; 
  

I am using DB2 for performing the below update operation.

update DATA set B_DESC=P_DESC, P_DESC=null
where B_DESC= *, P_DESC=*
(Select B_DESC,P_DESC from C_DATA) 

The below is actually possible but since complex joins are involved in that sub query it is not advisable to use like below

update DATA set B_DESC=P_DESC, P_DESC=null
where B_DESC= (Select B_DESC from C_DATA), P_DESC=(Select P_DESC from C_DATA)

I have to update DATA table, but the B_DESC and P_DESC i have to fetch it from C_DATA table and use it in the UPDATE query.

Please let me know how to do it. It has to be a single query if possible.

Thanks in advance.

解决方案

Use a merge query to update the table, instead of join. DB2 does not accept join in update query for that purpose, you have to use a merge:

MERGE INTO TABLE_NAME1 A 
    USING (SELECT  COL1, COL2  FROM TABLE_NAME2) B
    ON A.COL1 = B.COL2
    WHEN MATCHED AND A.COL1 = B.COL2
    THEN UPDATE SET A.COL1 = B.COL2;

这篇关于具有连接查询的DB2更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！