一、作用:
满足更新,不满足插入。
二、语法:
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)