MERGE INTO学习总结

一、前言

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

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

MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过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); 

严格意义上讲，在一个同时存在Insert和Update语法的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 )；