如何使用联接更新DB2表？

Question

我有两个表，我想通过加入它们进行更新。我正在使用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中做到这一点？

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)

Answer 1

您正在寻找以下MERGE语句：

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;