用B表数据更新A表数据

update a set a.ye=(select b.ye from a, b where b.xmdh=a.xmdh)

 

MERGE INTO DM_DELIVERYLIST_DETAILS DE

USING DM_DELIVERYLIST DM ON (DM.ORDER_NO=DE.ORDER_NO)

WHEN MATCHED THEN UPDATE SET DE.TASK_NO=DM.TASK_NO;

 

注意meger on 后面必须有括号；

 

方法一：

UPDATE DM_DELIVERYLIST DM

set DM.ORDER_NO=(SELECT T.order_no

FROM

(SELECT row_id,order_no

FROM (SELECT order_no ,ROWNUM nums FROM (SELECT DISTINCT order_no FROM DM_DELIVERYLIST_DETAILS) ) a

LEFT JOIN (SELECT row_id,ROWNUM nums FROM DM_DELIVERYLIST dm) b ON a.nums=b.nums) T

WHERE T.ROW_ID=DM.ROW_ID

)

WHERE  EXISTS(SELECT 1 FROM

(SELECT order_no ,ROWNUM nums FROM (SELECT DISTINCT order_no FROM DM_DELIVERYLIST_DETAILS) ) a

LEFT JOIN (SELECT row_id,ROWNUM nums FROM DM_DELIVERYLIST dm) b ON a.nums=b.nums) T

WHERE T.ROW_ID=DM.ROW_ID

 )

;

增where条件提高速度；

方法二：

MERGE INTO DM_DELIVERYLIST Dm

USING (SELECT row_id,order_no

FROM (SELECT order_no ,ROWNUM nums FROM (SELECT DISTINCT order_no FROM DM_DELIVERYLIST_DETAILS) ) a

LEFT JOIN (SELECT row_id,ROWNUM nums FROM DM_DELIVERYLIST dm) b ON a.nums=b.nums) T

ON (T.ROW_ID=DM.ROW_ID)

 

WHEN MATCHED THEN UPDATE SET DM.ORDER_NO=T.ORDER_NO;