首页 > 代码库 > Merge into(oracle)

Merge into(oracle)

2024-07-08 12:13:48   225人阅读

 

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

 

语法：

MERGE INTO table_name t1USING (table|view|sub_query) t2ON (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 C1USING (SELECT ‘isMonitor‘ AS OPERATION_KEY, ‘0001‘ AS USER_ID , ‘0‘ AS OPERATION_VALUE FROM DUAL) C2ON (C1.OPERATION_KEY = C2.OPERATION_KEY AND C1.USER_ID = C2.USER_ID)WHEN MATCHED THENUPDATE SET C1.OPERATION_VALUE = C2.OPERATION_VALUE WHERE C1.OPERATION_KEY = C2.OPERATION_KEY AND C1.USER_ID = C2.USER_IDWHEN NOT MATCHED THENINSERT (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 C1USING (SELECT ‘isMonitor‘ AS OPERATION_KEY, ‘0001‘ AS USER_ID , ‘1‘ AS OPERATION_VALUE FROM DUAL) C2ON (C1.OPERATION_KEY = C2.OPERATION_KEY AND C1.USER_ID = C2.USER_ID)WHEN MATCHED THENUPDATE SET C1.OPERATION_VALUE = C2.OPERATION_VALUE WHERE C1.OPERATION_KEY = C2.OPERATION_KEY AND C1.USER_ID = C2.USER_IDWHEN NOT MATCHED THENINSERT (OPERATION_KEY,OPERATION_VALUE,USER_ID) VALUES(C2.OPERATION_KEY,C2.OPERATION_VALUE,C2.USER_ID);

 

 

 

 


联系
我们