SQL> create table t1(name varchar2(20),money int);


Table created.


SQL> insert into t1 values('a',10);


1 row created.


SQL> insert into t1 values('b',20);


1 row created.


SQL> commit;


Commit complete.


SQL> create table t2(name varchar2(20),money int);


Table created.


SQL> insert into t2 values('a',30);


1 row created.


SQL> insert into t2 values('c',20);


1 row created.


SQL> commit;


Commit complete.


SQL> edit
Wrote file afiedt.buf


  1  merge into t2
  2  using t1
  3  on (t2.name=t1.name)
  4  when matched then
  5     update set t2.money=t1.money+t2.money
  6  when not matched then
  7*    insert values(t1.name,t1.money)
SQL> /


2 rows merged.


SQL> select * from t1;


NAME                                                              MONEY
------------------------------------------------------------ ----------
a                                                                    10
b                                                                    20


SQL> select * from t2;


NAME                                                              MONEY
------------------------------------------------------------ ----------
a                                                                    40
c                                                                    20
b                                                                    20


(1)update , insert只出现其中一个
SQL> merge into t2
  2  using t1
  3  on (t2.name=t1.name)
  4  when matched then
  5    update set t2.money=t2.money+t1.money;


1 row merged.


SQL> merge into t2
  2  using t1
  3  on (t2.name=t1.name)
  4  when not matched
  5  then
  6  insert values (t1.name,t1.money);


1 row merged.


(2) merge语句加条件
SQL> merge into t2
  2  using t1
  3  on (t2.name=t1.name)
  4  when matched then
  5    update set t2.money=t2.money+t1.money where name='b';


0 rows merged.


(3) delete自居清除行
SQL> edit
Wrote file afiedt.buf


  1   merge into t2
  2   using t1
  3   on(t2.name=t1.name)
  4   when matched then
  5   update set t2.money=t2.money+t1.money
  6*    delete where t2.name='a'
SQL> /
SQL>  select * from t1;


NAME            MONEY
---------- ----------
a                  10
b                  20


SQL> select * from t2;


NAME            MONEY
---------- ----------
c                  20


(4)无条件方式insert
SQL> merge into t2
  2  using t1
  3  on(1=2)
  4  when not matched then
  5  insert values(t1.name,t1.money);


2 rows merged.


(5)无法获得一组稳定的行
SQL> select * from t1;


NAME            MONEY
---------- ----------
b                  20
a                  10
a                  30


SQL> select * from t2;


NAME            MONEY
---------- ----------
a                  30
c                  20


SQL> merge into t2
  2  using t1
  3  on (t2.name=t1.name)
  4  when matched then
  5    update set t2.money=t2.money;


2 rows merged.


SQL> rollback;


Rollback complete.


SQL> edit
Wrote file afiedt.buf


  1* rollback
SQL> merge into t2
  2  using t1
  3  on (t2.name=t1.name)
  4  when matched then
  5     update set t2.money=t2.money+t1.money;
using t1
      *
ERROR at line 2:
ORA-30926: unable to get a stable set of rows in the source tables


修改
SQL> merge into t2
  2  using (select name,sum(money) as money from t1 group by name) t1
  3  on (t1.name=t2.name)
  4  when matched then
  5    update set t2.money=t1.money+t2.money;


1 row merged.


t2和t1相互调换位置，看一下出什么情况
SQL> select * from t1;


NAME            MONEY
---------- ----------
b                  20
a                  10
a                  30


SQL> select * from t2;


NAME            MONEY
---------- ----------
a                  30
c                  20


SQL> merge into t1
  2  using t2
  3  on (t2.name=t1.name)
  4  when matched then
  5    update set t1.money=t1.money+t2.money;


2 rows merged.
可以正常执行