MERGE INTO学习总结

举报
SHQ5785 发表于 2022/08/03 21:45:13 2022/08/03
【摘要】 一、前言在工作中,查看到类似于如下的SQL语句: MERGE INTO user.CUSBREACHTAB A USING (SELECT C.CUSTOMERID, -- 客户ID C.CUSTOMERCODE, -- 客户号 C.USERORGCDE, -- 区域编号 C.CUSTOME...

一、前言

在工作中,查看到类似于如下的SQL语句:

 MERGE INTO user.CUSBREACHTAB A 
  USING (SELECT C.CUSTOMERID, -- 客户ID 
                C.CUSTOMERCODE, -- 客户号 
                C.USERORGCDE, -- 区域编号 
                C.CUSTOMERNAME, -- 客户名称 

MERGE语句是Oracle9i新增的语法,用来合并UPDATEINSERT语句。通过MERGE语句,根据一张表或子查询的连接条件对另外一张表进行查询,连接条件匹配上的进行UPDATE,无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERT+UPDATE

例如,有一个表T,有两个字段a、b,我们想在表T中做Insert/Update,如果条件满足,则更新T中b的值,否则在T中插入一条记录。在Microsoft的SQL语法中,很简单的一句判断就可以了,SQL Server中的语法如下:

if exists(select 1 from T where T.a='1001' )
    update T set T.b=2 Where T.a='1001' 
else 
    insert into T(a,b) values('1001',2);

Oracle中,要实现相同的功能,要用到Merge into来实现(Oracle 9i引入的功能),其语法如下:

MERGE INTO table_name alias1
USING (table|view|sub_query) alias2
ON (join condition) 
WHEN MATCHED THEN 
    UPDATE table_name 
    SET col1 = col_val1, 
           col2 = col_val2 
WHEN NOT MATCHED THEN 
    INSERT (column_list) VALUES (column_values); 

严格意义上讲,在一个同时存在InsertUpdate语法的Merge语句中,总共Insert/Update的记录数,就是Using语句中alias2的记录数”。所以,要实现上面的功能,可以这样写:

MERGE INTO T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) 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);

二、演示

//表1
create table subs(msid number(9),
                  ms_type char(1),
                 areacode number(3)
                 );
//表2
create table acct(msid number(9),
                  bill_month number(6),
                  areacode   number(3),
                  fee        number(8,2) default 0.00);
                  
//测试数据                  
insert into subs values(905310001,0,531);
insert into subs values(905320001,1,532);
insert into subs values(905330001,2,533);
commit;

2.1 基本功能

2.1.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
   when NOT MATCHED then
        insert(msid,bill_month,areacode) 
        values(b.msid,'200702',b.areacode);

2.1.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);

2.1.3 只有matched clause, 也就是只更新不插入

   merge into acct a 
     using subs b on (a.msid=b.msid)
   when MATCHED then
        update set a.areacode=b.areacode

三、10g增强介绍

3.1 条件操作

3.1.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;

3.1.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.1.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;

3.1.4 删除操作

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);

四、注意

  1. MERGE语句的UPDATE不能修改用于连接的列,否则会报错(on 后面的条件列就是联接);
  2. using 后面可以是(SELECT msid ,areacode FROM subs GROUP by msid );
【版权声明】本文为华为云社区用户原创内容,未经允许不得转载,如需转载请自行联系原作者进行授权。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

举报
请填写举报理由
0/200