Sqlserver、oracle中Merge的使用方法,一个merge语句搞定多个Insert,Update,Delete操作

SQLServer官方文档 https://learn.microsoft.com/zh-cn/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16
Oracle官方文档https://docs.oracle.com/database/121/SQLRF/statements_9017.htm#SQLRF01606
Postgresql官方文档https://www.postgresql.org/docs/15/sql-merge.html

总结
1、Merge的出现，实现了一个语句就能搞定Insert,Update,Delete的同时操作
2、merge语句最后的分号不可以省略
3、WHEN NOT MATCHED BY TARGET表示目标表不匹配，BY TARGET是默认的，所以上面我们直接使用WHEN NOT MATCHED THEN就表示WHEN NOT MATCHED BY TARGET
4、WHEN NOT MATCHED BY SOURCE表示源表不匹配，即目标表中存在，源表中不存在的情况
5、merge语句中的insert、update、delete子语句后面虽然无法直接加where条件的关键字来过滤，但是可以在when MATCHED 或 WHEN NOT MATCHED后面加and来过滤
6、oracle的merge into也是差不多这个功能，不过oracle可以这直接加where条件来过滤，postgresql 15开始有有merge into这个功能、mysql目前没有merge这个语句

SQL Server的例子

语法格式：
MERGE <target_table>
USING <table_source>
ON <merge_search_condition>
WHEN MATCHED THEN
<merge_matched> ]
WHEN NOT MATCHED [BY TARGET] THEN
语句;

主要用法：
merge无法多次更新同一行，也无法更新和删除同一行
当源表和目标表不匹配时：
若数据是源表有目标表没有，则进行插入操作
若数据是源表没有而目标表有，则进行更新或者删除数据操作
当源表和目标表匹配时：
进行更新操作或者删除操作

create table Sourse1(sid int,sname varchar(50),ssex char(10))
create table Target1(tid int,tname varchar(50),tsex char(10))
insert into Sourse1 values (1,‘1’,‘male’),(2,‘2’,‘female’),(3,‘3’,‘male’),(5,‘5’,‘female’),(7,‘7’,‘female’),(9,‘9’,‘female’)
insert into Target1 values (1,‘1’,‘male’),(2,‘2’,‘male’),(3,‘3’,‘male’),(4,‘4’,‘male’),(6,‘6’,‘male’),(8,‘8’,‘male’),(10,‘10’,‘male’)

select * from Sourse1
sid sname ssex
1 1 male
2 2 female
3 3 male
5 5 female
7 7 female
9 9 female

select * from Target1
tid tname tsex
1 1 male
2 2 male
3 3 male
4 4 male
6 6 male
8 8 male
10 10 male

如果源表和目标表ID匹配，就把目标表修改成源表，如果不匹配，则把源表数据插入目标表
也就是把目标表tid=2的ssex值从male改成源表的sid=2的ssex值female，并插入源表sid=5\7\9的记录到目标表

merge Target1 as t --目标表
using Sourse1 as s --源表
on t.tid=s.sid --关联关系
when MATCHED then --如果相匹配
update set t.tsex=s.ssex --修改
WHEN NOT MATCHED THEN --如果匹配不上
insert (tid,tname,tsex) values (s.sid,s.sname,s.ssex); --新增

结果如下
select * from Target1
tid tname tsex
1 1 male
2 2 female
3 3 male
4 4 male
6 6 male
8 8 male
10 10 male
5 5 female
7 7 female
9 9 female

恢复原样
delete from Target1 where tid in (5,7,9)
update Target1 set tsex=‘male’ where tid=2

加上限制条件
如果ID范围限制是2以内，源表和目标表ID匹配，就把目标表修改成源表
如果ID范围限制在5以内，源表和目标表ID不匹配，则把源表数据插入目标表
也就是只插入源表sid=5的记录到目标表

merge Target1 as t --目标表
using Sourse1 as s --源表
on t.tid=s.sid --关联关系
when MATCHED and s.sid<2 then --如果源表范围内sid<2相匹配
update set t.tsex=s.ssex --修改
WHEN NOT MATCHED and s.sid<=5 THEN --如果源表范围内sid<=5匹配不上
insert (tid,tname,tsex) values (s.sid,s.sname,s.ssex); --新增

结果如下
select * from Target1
tid tname tsex
1 1 male
2 2 male
3 3 male
4 4 male
6 6 male
8 8 male
10 10 male
5 5 female

Oracle的例子

当存在记录时，就更新(Update)，不存在数据时，就插入(Insert)。
merge into 目标表 a
using 源表 b
on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)
when matched then update set a.更新字段=b.字段
when not macthed then insert into a(字段1,字段2……)values(值1,值2……)

create table merge_a(aid number,aname varchar2(12));
create table merge_b(bid number,bname varchar2(12));

insert into merge_a values(1,‘1’);
insert into merge_a values(2,‘2’);
insert into merge_a values(3,‘3’);
insert into merge_a values(4,‘4’);

insert into merge_b values(4,‘4’);
insert into merge_b values(5,‘5’);
insert into merge_b values(6,‘6’);
commit;

select * from merge_b

–不存在当merge_a.aname = '3’时merge_a.aid=merge_b.bid的情况，所以执行insert
merge into merge_b b using (select * from merge_a where aname = ‘3’) a
on(b.bid=a.aid)
when matched then
update set b.bname=b.bname+100
when not matched then
insert(b.bid,b.bname) values(7,‘7’) ;

select * from merge_b

–存在当merge_a.aname = '4’时merge_a.aid=merge_b.bid的情况，所以执行update
merge into merge_b b using (select * from merge_a where aname = ‘4’) a
on(b.bid=a.aid)
when matched then
update set b.bname=b.bname+100
when not matched then
insert(b.bid,b.bname) values(7,‘7’) ;