SQL官方文件 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

總結
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、mysql的merge into也是差不多這個功能，不過oracle可以這直接加where條件來過濾，postgresql目前沒有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') ;</merge_matched></merge_search_condition></table_source></target_table>

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

相關文章