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;