Oracle数据库中MergeInto语法作用和实例

一、作用：

满足更新，不满足插入。

二、语法：

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)