[Oracle]Merge Into

一、作用：

• 满足更新，不满足插入。

二、语法：

• MERGE INTO [target-table] A USING [source-table sql] B
• ON ([conditional expression] and […]…)
• WHEN MATCHED THEN
  
  • [UPDATE sql]
• WHEN NOT MATCHED THEN
  
  • [INSERT sql]

三、举例：

MERGE INTO 表1 T1
USING (SELECT 列 AS a,常量 AS b FROM 表2) 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);

以上效果等同于
update 表1 set 表1.b=表2.b where exists (select 1 from 表1 T1，表2 T2 where T1.a=T2.a)
insert into 表1(a,b) （select T2.a,T2.b from 表2 T2） where not exists (select 1 from 表1 T1，表2 T2 where T1.a=T2.a)