Oracle merge into 用法

不同表

MERGE INTO  TABLE_A TA
USING (SELECT *  FROM TABLE_B TB) TB 
    ON (TA.ID=TB.ID)
WHEN MATCHED 
THEN 
    UPDATE SET TA.NAME=TB.NAME
WHEN NOT MATCHED 
THEN  
    INSERT (TA.ID, TA.NAME) VALUES (TB.ID, TB.NAME );

同表

注意：为空时不进行merge，需使用DUAL或者计数大于0

• 例1

MERGE INTO T T1 
USING (SELECT '1001' AS a,2 AS b FROM dual) T2
    ON (T1.a=T2.a)
WHEN MATCHED THEN   
    UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN     
    INSERT (a,b) VALUES(T2.a,T2.b);

• 例2

MERGE INTO T T1 
USING (SELECT count(1) cnt FROM t where t.a='1001') T2
    ON ( T2.cnt>0)
WHEN MATCHED THEN   
    UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN     
    INSERT (a,b) VALUES('1001',2);