如何使用连接更新 DB2 表? [英] How to update DB2 table with a join?

问题描述

我有两个表，我想通过加入它们来更新.我正在使用 DB2 V9.7.

I have two tables and I want to update by joining them. I am using DB2 V9.7.

ORDER_APPROVALS

ORDER_ID    CREATED_BY_ID   CREATED_BY_NAME PROCESS_DT           
-------------------------------------------------------
234         2               admin           (null)    
307         2               admin           (null)    
313         2               admin           11-11-2013    

ORDER_ATTRIBUTE

ORDER_ID    ATTRIBUTE_ID    VALUE
-----------------------------------
234         123             ? --(ORDER_APPROVALS.CREATED_BY_NAME)
307         123             ? --(ORDER_APPROVALS.CREATED_BY_NAME)

我想根据 Attribute_ID 123 更新值字段.到目前为止，我尝试了以下查询.但这不起作用.我在 Netezza 中尝试过类似的加入方式，而且效果很好.想知道，如何在 DB2 中做到这一点?

I want to update value field against Attribute_ID 123. So far I tried following query. But that does not work. I have tried similar kind of join in Netezza and that works. Want to know, how to do it in DB2?

update ORDER_ATTRIBUTE OT set OT.VALUE = 
(select CREATED_BY_NAME from ORDER_APPROVALS OA 
where OA.ORDER_ID = OT.ORDER_ID and OA.PROCESS_DT is NULL) 
where OT.ATTRIBUTE_ID = 123 and OT.ORDER_ID in 
(select ORDER_ID from ORDER_APPROVALS where PROCESS_DT is NULL)

推荐答案

您正在寻找 MERGE 语句:

You are looking for the MERGE statement:

merge into ORDER_ATTRIBUTE ot
    using (select ORDER_ID, CREATED_BY_NAME
             from ORDER_APPROVALS
            where PROCESS_DT is null) oa
    on
       (ot.ORDER_ID = oa.ORDER_ID)
    when matched 
     and ot.ATTRIBUTE_ID = 123
    then update set VALUE = oa.CREATED_BY_NAME;

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