sql server 中自2008后引入merge 关键字,为学习使用,下面写个小脚本
use Test;
-- 创建表
create table t1(
id int,
score int
)
create table t2(
id int,
name varchar(24),
score int
)
--2 插入测试数据
insert into t1
values(1,23),(2,34),(3,235425)
insert into t2
values(1,'name1',225),(2,'name2',225),(3,'name3',225),(4,'name4',225)
--3 使用merge ,有相同记录更新,不存在 插入新记录
merge into t1
using t2 on t1.id = t2.id
when matched then
update set t1.score = t2.score
when not matched then
insert (id,score)
values(t2.id,t2.score);
-- select * from t1
=============merge into 支持以下几种方式===
1) matched 和not matched clauses 同时使用
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
where b.ms_type=0
when NOT MATCHED then
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode)
where b.ms_type=0;
2) 只有not matched clause,也就是只插入不更新
merge into acct a
using subs b on (a.msid=b.msid)
when NOT MATCHED then
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode)
where b.ms_type=0;
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
where b.ms_type=0
when NOT MATCHED then
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode)
where b.ms_type=0;
2) 只有not matched clause,也就是只插入不更新
merge into acct a
using subs b on (a.msid=b.msid)
when NOT MATCHED then
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode)
where b.ms_type=0;
3) 只有matched clause, 也就是只更新不插入
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
where b.ms_type=0;
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
where b.ms_type=0;
4)matched时删除
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
delete where (b.ms_type!=0);
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
delete where (b.ms_type!=0);