Merge into(oracle)

 

作用：使用一条sql语句进行insert或者update操作，如果存在就update，如果不存在就insert

 

语法：

MERGE INTO table_name t1
USING (table|view|sub_query) t2
ON (join condition) 
WHEN MATCHED THEN 
    UPDATE table_name 
    SET col1 =.., 
        col2 =..WHEN NOT MATCHED THEN 
    INSERT (..) VALUES (..); 

 

注意：由于Merge在oracle中最先是用于整表更新，所以t2中的数据每一条都会和t1进行on中的条件比对

　　  insert或者update的记录数和t2中的记录数相同，当然也可在update后加where条件控制

 

eg：

 

MERGE INTO CUSTOMIZATION C1
USING (SELECT 'isMonitor' AS OPERATION_KEY, '0001' AS USER_ID , '0' AS OPERATION_VALUE FROM DUAL) C2
ON (C1.OPERATION_KEY = C2.OPERATION_KEY AND C1.USER_ID = C2.USER_ID)
WHEN MATCHED THEN
UPDATE SET C1.OPERATION_VALUE = C2.OPERATION_VALUE WHERE C1.OPERATION_KEY = C2.OPERATION_KEY AND C1.USER_ID = C2.USER_ID
WHEN NOT MATCHED THEN
INSERT (OPERATION_KEY,OPERATION_VALUE,USER_ID) VALUES(C2.OPERATION_KEY,C2.OPERATION_VALUE,C2.USER_ID);

 

 

首次插入时表中没有符合条件数据，则插入数据，

 

 

只更改operation_value, operation_key 和user_id存在时更新数据,operation_value更新为1

MERGE INTO CUSTOMIZATION C1
USING (SELECT 'isMonitor' AS OPERATION_KEY, '0001' AS USER_ID , '1' AS OPERATION_VALUE FROM DUAL) C2
ON (C1.OPERATION_KEY = C2.OPERATION_KEY AND C1.USER_ID = C2.USER_ID)
WHEN MATCHED THEN
UPDATE SET C1.OPERATION_VALUE = C2.OPERATION_VALUE WHERE C1.OPERATION_KEY = C2.OPERATION_KEY AND C1.USER_ID = C2.USER_ID
WHEN NOT MATCHED THEN
INSERT (OPERATION_KEY,OPERATION_VALUE,USER_ID) VALUES(C2.OPERATION_KEY,C2.OPERATION_VALUE,C2.USER_ID);