ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 《剑破冰山》读书之merge

《剑破冰山》读书之merge

原创 Linux操作系统 作者:zzuiezhangqihui 时间:2011-03-26 15:29:04 0 删除 编辑
一、认识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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-02-19

  • 博文量
    9
  • 访问量
    6634