merge应用一例

如何把id 为1和2的两列的值互换？

scott@LS1>create table test(id number,name varchar2(20));  

Table created.

scott@LS1>insert into test values(1,'a');

1 row created.

scott@LS1>insert into test values(2,'b');

1 row created.

scott@LS1>commit;

Commit complete.

scott@LS1>select * from test;

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

当你执行update test  set name =（select name from test where id =2 ) where id =1;

这个时候id=1 的那么的值已经改变了，不能实现目标

思路一：

所以我们需要构建一个虚表，把原来的值存储起来，然后和原表merge

scott@LS1>select 1 id,(select name from test where id=2) name from dual
  2  union all
  3  select 2,(select name from test where id=1) from dual;

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

scott@LS1>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) from dual) t
  5  on (test.id = t.id)
  6  when matched then update set test.name = t.name;

2 rows merged.

scott@LS1>select * from test;

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

思路二：

直接把真实结果用select 的方式取出，然后利用这个结果集更新原表记录。

scott@LS1>l
  1    merge into test using
  2    (
  3    with t as
  4    (select 1 id,(select name from test where id=2 ) name from dual
  5    union all
  6    select 2,(select name from test where id=1 ) from dual)
  7    select test.id,test.rowid as rn,t.name from test,t
  8    where test.id = t.id
  9    )n
 10    on(test.rowid = n.rn)
 11    when matched then update
 12*  set test.name = n.name
scott@LS1>/

2 rows merged.


scott@LS1>select * from test;

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

仅仅是查询的话，可以使用：

scott@LS1>with t as
  2  (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) from dual)
  5  select test.id, t.name from test,t
  6  where test.id = t.id;

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