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);

 

 

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

Merge into(oracle)

 

 

只更改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);

Merge into(oracle)

 

 

 

 

Merge into(oracle),布布扣,bubuko.com

Merge into(oracle)

上一篇:百分点内存数据库架构演变


下一篇:oracle