MERGE INTO：存在就更新不存在就新增——oracle

时间 2019-12-14 标签 merge 存在更新存在新增 oracle

MERGE INTO [your table-name] [rename your table here]

USING ( [write your query here] )[rename your query-sql and using just like a table]

ON ([conditional expression here] AND [...]...)

WHEN MATCHED THEN [here you can execute some update sql or something else ]

WHEN NOT MATCHED THEN [execute something else here ! ]

做用:判断Ｂ表和Ａ表是否知足ON中条件，若是知足则执行知足后面的update SQL，若是不知足，则执行不知足后面的insert SQL。sql

例子：数据库

使用状况，例如：若是数据库中存在数据就update，若是不存在就insert。express

MERGE INTO  TEST T1

USING (SELECT '2' as ID, 'newtest2' as NAME FROM dual) T2 on (T1.ID=T2.ID)

WHEN MATCHED THEN UPDATE SET T1.NAME=T2.NAME

WHEN NOT MATCHED THEN  INSERT (T1.ID, T1.NAME) VALUES (T2.ID, T2.NAME );