oracle使用 merge 更新或插入数据（总结）

转自：http://xo-tobacoo.iteye.com/blog/182791

总结下。使用merge比传统的先判断再选择插入或更新快很多。 
1）主要功能 
提供有条件地更新和插入数据到数据库表中 
如果该行存在，执行一个UPDATE操作，如果是一个新行，执行INSERT操作 
    — 避免了分开更新 
    — 提高性能并易于使用 
    — 在数据仓库应用中十分有用 

2）MERGE语句的语法如下： 

MERGE [hint] INTO [schema .] table [t_alias] USING [schema .] 

{ table | view | subquery } [t_alias] ON ( condition ) 

WHEN MATCHED THEN merge_update_clause 

WHEN NOT MATCHED THEN merge_insert_clause; 


还是看例子就知道怎么回事： 
MERGE INTO copy_emp c    
USING employees e    
ON (c.employee_id=e.employee_id)    
WHEN MATCHED THEN   
UPDATE SET   
c.first_name=e.first_name,    
c.last_name=e.last_name,    
c.department_id=e.department_id    
WHEN NOT MATCHED THEN   
INSERT VALUES(e.employee_id,e.first_name,e.last_name,    
e.email,e.phone_number,e.hire_date,e.job_id,    
e.salary,e.commission_pct,e.manager_id,    
e.departmetn_id);  
MERGE INTO copy_emp c 
USING employees e 
ON (c.employee_id=e.employee_id) 
WHEN MATCHED THEN 
UPDATE SET 
c.first_name=e.first_name, 
c.last_name=e.last_name, 
c.department_id=e.department_id 
WHEN NOT MATCHED THEN 
INSERT VALUES(e.employee_id,e.first_name,e.last_name, 
e.email,e.phone_number,e.hire_date,e.job_id, 
e.salary,e.commission_pct,e.manager_id, 
e.departmetn_id); 


3）使用merge的注意事项： 
创建测试表： 
CREATE TABLE MM (ID NUMBER, NAME VARCHAR2(20)); 
CREATE TABLE MN (ID NUMBER, NAME VARCHAR2(20)); 
插入数据 
INSERT INTO MM VALUES (1, 'A'); 
INSERT INTO MN VALUES (1, 'B'); 
执行： 
MERGE INTO MN A 
    USING MM B 
    ON(A.ID=B.ID) 
    WHEN MATCHED THEN 
    UPDATE SET A.ID = B.ID 
    WHEN NOT MATCHED THEN 
    INSERT VALUES(B.ID, B.NAME); 
ON(A.ID=B.ID) 
报错，原因是on子句的使用的字段不能够用于update，即Oracle不允许更新用于连接的列 
修改： 
MERGE INTO MN A 
    USING MM B 
    ON(A.ID=B.ID) 
    WHEN MATCHED THEN 
    UPDATE SET A.NAME = B.NAME 
    WHEN NOT MATCHED THEN 
    INSERT VALUES(B.ID, B.NAME); 
ON(A.ID=B.ID) 

再插入：INSERT INTO MM VALUES (1, 'C'); 
再执行： 
MERGE INTO MN A 
    USING MM B 
    ON(A.ID=B.ID) 
    WHEN MATCHED THEN 
    UPDATE SET A.NAME = B.NAME 
    WHEN NOT MATCHED THEN 
    INSERT VALUES(B.ID, B.NAME); 
ON(A.ID=B.ID) 
报错，原因无法在源表中获得一组稳定的行 

4）更新同一张表的数据。需要注意下细节，因为可能涉及到using的数据集为null,所以要使用count()函数。 
MERGE INTO mn a 
   USING (select count(*) co from mn where mn.ID=4) b 
   ON (b.co<>0)--这里使用了count和<>，注意下，想下为什么！ 
   WHEN MATCHED THEN 
      UPDATE 
         SET a.NAME = 'E' 
         where a.ID=4 
   WHEN NOT MATCHED THEN 
      INSERT 
      VALUES (4, 'E'); 

------------------------------------------------------------------------------------------------------

转自：http://chenxy.blog.51cto.com/729966/823342

Merge into 详细介绍

　　MERGE语句是 oracle 9i 新增的语法，用来合并 UPDATE 和 INSERT 语句。

　　通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，

　　连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。

　　这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。

　　

 

drop table t;
CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
drop table t1;
CREATE TABLE T1 AS
SELECT ROWNUM ID, OWNER, TABLE_NAME, CAST('TABLE' AS VARCHAR2(100)) OBJECT_TYPE FROM DBA_TABLES;

MERGE INTO T1 USING T
ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME AND T.OBJECT_TYPE = T1.OBJECT_TYPE)
WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);

--insert后面不写表示插入全部列

 

MERGE INTO T1 USING T
ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);

--常见错误，连接条件不能获得稳定的行，可以使用下面的用子查询

MERGE INTO T1
USING (SELECT OWNER, OBJECT_NAME, MAX(ID) ID FROM T GROUP BY OWNER, OBJECT_NAME) T
ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME);

SELECT ID, OWNER, OBJECT_NAME, OBJECT_TYPE FROM T
MINUS
SELECT * FROM T1;
drop table subs;
create table subs(

msid number(9),
ms_type char(1),
areacode number(3)
);

drop table acct;
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;

merge into acct a --操作的表
using subs b on (a.msid=b.msid) --使用原始数据来源的表，并且制定条件，条件必须有括号
when matched then
update set a.areacode=b.areacode

--当匹配的时候，执行update操作，和直接update的语法不一样，不需要制定表名
when not matched then

--当不匹配的时候，执行insert操作，也不需要制定表名，若指定字段插入，

--则在insert后用括号标明，不指定是全部插入
insert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode);
--另外，MERGE语句的UPDATE不能修改用于连接的列，否则会报错

select * from acct;
select * from subs;
--10g新特性，单个操作

merge into acct a
using subs b on(a.msid=b.msid)
when not matched then

--只有单个not matched的时候，只做插入，不做更新，只有单个matched的时候，只做更新操作
      insert(a.msid,a.bill_month,a.areacode) values(b.msid,'200702',b.areacode);
      update acct set areacode=800 where msid=905320001;
      delete from acct where areacode=533 or areacode=531;
      insert into acct values(905320001,'200702',800,0.00);
--删除重复行
delete from subs b where b.rowid<(
   select max(a.rowid)

   from subs a

   where a.msid=b.msid and a.ms_type=b.ms_type and a.areacode=b.areacode);
   --10g新特性，merge操作之后，只有匹配的update操作才可以，

   --用delete where子句删除目标表中满足条件的行。

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)
when NOT MATCHED then
      insert(msid,bill_month,areacode)

      values(b.msid,'200702',b.areacode)
      where b.ms_type=0;
--10g新特性，满足条件的插入和更新

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;

select * from subs where ms_type=0;