sql - una - update inner join db2

¿Cómo actualizar la tabla de DB2 con una combinación?

Question

Tengo dos tablas y quiero actualizar uniéndome a ellas. Estoy usando 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)

Quiero actualizar el campo de valor contra Attribute_ID 123. Hasta ahora he intentado seguir la consulta. Pero eso no funciona. He intentado un tipo similar de unión en Netezza y eso funciona. ¿Quieres saber cómo hacerlo en 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

Creo que debes usar una tabla derivada para lograr esto:

update order_attributes
set value = (
select
created_by_name
from(
select
created_by_name,
oa.order_id
from
order_approvals oa
left outer join order_attributes ot
on oa.order_id = ot.order_id
AND OT.ATTRIBUTE_ID = 123
and OT.ORDER_ID in
(select ORDER_ID from ORDER_APPROVALS where PROCESS_DT is NULL)
) ORDERS
WHERE orders.order_id = order_attributes.order_Id
)

Answer 2

Usted está buscando la declaración 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;