《剑破冰山》读书之merge

一、认识merge

 

merge=update+insert。

 

存在update ，不存在insert。

 

merge into table t1 using {table/view/subquery} t2

on t1.c1=t2.c1

when matched then update t1.c2=t2.c2

when not matched then insert t1 values(t2.c1,t2.c2)

。

 

二、oracle10g中的merge 

 

1、在10g中可以没有insert部分或者没有update部分。

merge=update(存在update)

或者

merge=insert（不存在insert）

。

在9i之前的版本必须两者都存在，缺一不可。

 

2、可以在merge中增加where条件。

 

merge into t2 using t1

on (t1.name=t2.name)

when matched then

update  set t2.money=t1.money+t2.money

where t1.name='A'

;

3、可以有delete

merge into t2 using t1

on (t1.name=t2.name)

when matched then

update  set t2.money=t1.money+t2.money

delete where t1.name='A'

;

 

4、可以无条件insert

merge into t2 using t1

on (1=2)               --关键在于这里的条件永远不成立

when no matched then

insert values(t1.name,t1.money)

;

 

三、merge的误区

 

1、必须在原表中有确定的一条记录using后面的对象，否则产生ora-30926错误。

SQL> SELECT * FROM T1;
 
NAME                                                   MONEY
-------------------- ---------------------------------------
A                                                         10
A                                                         30
B                                                         20
 
SQL> SELECT * FROM T2;
 
NAME                                                   MONEY
-------------------- ---------------------------------------
A                                                         30
C                                                         20

 

SQL> MERGE INTO T2 USING T1 ON (T1.NAME=T2.NAME) WHEN MATCHED THEN UPDATE set T2.MONEY=T2.MONEY+T1.MONEY ;
 
MERGE INTO T2 USING T1 ON (T1.NAME=T2.NAME) WHEN MATCHED THEN UPDATE set T2.MONEY=T2.MONEY+T1.MONEY
 
ORA-30926: unable to get a stable set of rows in the source tables

 

oracle中的merge语句应该保证on中条件的唯一性。可以在t1和t2表的关联字段建主键，这样基本上避免了此类问题。merge字段的关联的字段互为主键，merge效率比较高。

所以上面的语句改写成。

SQL> MERGE INTO T2 USING (select name,sum(money) money from t1 group by name)T1 ON (T1.NAME=T2.NAME) WHEN MATCHED THEN UPDATE set T2.MONEY=T2.MONEY+T1.MONEY ;
 
Done

如果基表有多条记录（t2有多条记录），t1一条记录，纳秒更新时候是不会出错的。

 

2、delete子句必须在最后

 

merge into t2

using t1

on(t1.name=t2.name)

when matched then update set t2.money=t2.money+t1.money

where t1.name='A'

delete where (t2.name='C')

;

 

有delete条件是在on中能够匹配到的。

merge into t2

using t1

on(t1.name=t2.name)

when not matched then insert values(t1.name,t1.money)

when matched then

update set t2.money=t2.money

delete where (t2.name='A')

;

 

3、

同一张表的更新问题。

 

如果更新一张表，往一个表中增加一行记录。

SQL> select * from t2;

NAME                      MONEY
-------------------- ----------
C                            20
B                            20
A                            10

SQL> merge into t2 using(select * from t2 where name='D') t
  2  on (t.name=t2.name)
  3  when matched then
  4  update set t2.money=100
  5  when not matched then
  6  insert values('D',200)
  7  ;

0 rows merged.

SQL> select * from t2;

NAME                      MONEY
-------------------- ----------
C                            20
B                            20
A                            10

 

 

可以看出在构造表的时候 条件on(t.name=t2.name) 原因是using中必须有要插入或者更新的那一行记录。

所以改写成。

SQL> merge into t2 using(select count(*) cou from t2 where name='D') t
  2  on (t.cou<>0)
  3  when matched then update
  4  set t2.money=100
  5  when not matched then
  6  insert values('D',200)
  7  ;

1 row merged.

SQL> select * from t2;

NAME                      MONEY
-------------------- ----------
C                            20
B                            20
A                            10
D                           200

 

4、merge巧妙用法

 

对于一个表中的两条记录的更新。

 

SQL> create table test(id integer, name varchar2(20));

Table created.

SQL> insert into test values(1,'a');

1 row created.

SQL> insert into test values(2,'b');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

        ID NAME
---------- --------------------
         1 a
         2 b

如果更新1,2 的名字，名字互换。

可以用过程实现。用merge实现如下

SQL> merge into test
  2  using (select 1 id,(select name from test where id=2) name from dual
  3   union all
  4  select 2,(select name from test where id=1) name from dual)
  5   t
  6  on (test.id=t.id)
  7  when matched then update set test.name=t.name
  8  ;

2 rows merged.

 

SQL> select * from test;

        ID NAME
---------- --------------------
         1 b
         2 a

 

也可以利用构造虚拟表的方式直接取出结果。

如：

SQL> with t as
  2  (select 1 id,(select name from test where id=2) name from dual union all select 2,(select name from test where id=1) name from dual)
  3  select test.id , t.name from test,t
  4  where test.id=t.id
  5  ;

        ID NAME
---------- --------------------
         1 b
         2 a

关于with构造虚拟表的方法，可以参见《with构造虚拟表方法》

可以用with构造的虚拟表当成using后面的表去更新原表，具体sql不再写了。

可以结合rowid来使用。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25296295/viewspace-690570/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25296295/viewspace-690570/