我有两个表，我想通过加入它们来更新。我使用的是 DB2 V9.7。
订单批准
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_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)
您正在寻找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;
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
