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

问题描述

我有两张表，我想通过加入他们进行更新。我正在使用DB2 V9.7。

ORDER_APPROVALS

  ORDER_ID CREATED_BY_ID CREATED_BY_NAME PROCESS_DT 
 --------------------------------------------- ---------- 
 234 2 admin（null）
 307 2管理员（null）
 313 2管理员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中执行此操作？

 更新ORDER_ATTRIBUTE OT设置OT.VALUE = 
（选择CREATED_BY_NAME来自ORDER_APPROVALS OA 
其中OA.ORDER_ID = OT.ORDER_ID和OA.PROCESS_DT为NULL）
其中OT.ATTRIBUTE_ID = 123和
中的OT.ORDER_ID（从ORDER_APPROVALS中选择ORDER_ID，其中PROCESS_DT为NULL ）
  

解决方案

您正在寻找 MERGE 声明：

 合并到ORDER_ATTRIBUTE ot 
 using从ORDER_APPROVALS 
中选择ORDER_ID，CREATED_BY_NAME 
，其中PROCESS_DT为null）oa 
 on 
（ot.ORDER_ID = oa.ORDER_ID）
当匹配
和ot .ATTRIBUTE_ID = 123 
然后更新设置VALUE = oa.CREATED_BY_NAME; 
  

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)

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)

解决方案

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