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

问题描述

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

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)

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

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.

提前致谢.

推荐答案

使用合并查询来更新表，而不是联接.DB2 不接受为此目的加入更新查询，您必须使用合并:

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屋！