oracle merge into 测试

create table tt1(c1 int, c2 int);

create table tt2(c3 int, c4 int);

insert into tt1 values(1,1);
insert into tt1 values(2,2);
insert into tt1 values(2,3);
insert into tt1 values(4,4);

insert into tt2 values(2,22);
insert into tt2 values(2,222);

insert into tt2 values(5,5);
commit;

举例试验各种情况：
1.JOIN有匹配，但是不符合更新条件
  JOIN没有匹配，但是不符合插入条件
merge into tt1 using tt2 on(tt1.c1 = tt2.c3) when matched then update set t
t1.c2=tt2.c4 where tt1.c1>3 when not matched then insert values(tt2.c3, tt2.c4)
where tt2.c3>5;

0 行已合并。

2.有更新记录，但不符合DELETE条件，更新后的数据没有删除

merge into tt1 using tt2 on(tt1.c1 = tt2.c3) when matched then update set t
t1.c2=tt2.c4 delete where tt1.c1>2 when not matched then insert values(tt2.c3, t
t2.c4) where tt2.c3>5;

2 行已合并。

SQL> select * from tt1;

        C1         C2
---------- ----------
         1          1
         2        222
         2        222
         4          4

3. 有更新记录，符合DELETE条件，更新后的数据被删除
merge into tt1 using tt2 on(tt1.c1 = tt2.c3) when matched then update set t
t1.c2=tt2.c4 delete where tt1.c1=2 when not matched then insert values(tt2.c3, t
t2.c4) where tt2.c3>5;

2 行已合并。

SQL> select *from tt1;

        C1         C2
---------- ----------
         1          1
         4          4

4.有更新记录，删除条件在源表和目标表上都设置，如果不符合，没有被删除

SQL> merge into tt1 using tt2 on(tt1.c1 = tt2.c3) when matched then update set t
t1.c2=tt2.c4 delete where tt1.c1=2 and tt2.c3>2 when not matched then insert val
ues(tt2.c3, tt2.c4) where tt2.c3>5;

2 行已合并。

SQL> select * from tt1;

        C1         C2
---------- ----------
         1          1
         2        222
         2        222
         4          4

5. 有更新记录，删除条件在源表和目标表上都设置，如果符合，更新的记录被删除

SQL> merge into tt1 using tt2 on(tt1.c1 = tt2.c3) when matched then update set t
t1.c2=tt2.c4 delete where tt1.c1=2 and tt2.c3>1 when not matched then insert val
ues(tt2.c3, tt2.c4) where tt2.c3>5;

2 行已合并。

SQL> select * from tt1;

        C1         C2
---------- ----------
         1          1
         4          4

 

6.不符合更新条件，没有更新记录，即使符合DELETE条件，原始记录也不会被删除

SQL> merge into tt1 using tt2 on(tt1.c1 = tt2.c3) when matched then update set
t1.c2=tt2.c4 where tt1.c1 > 2 delete where tt1.c1=2 and tt2.c3=2 when not match
d then insert values(tt2.c3, tt2.c4) where tt2.c3>5;

0 行已合并。

SQL> select * from tt1;

        C1         C2
---------- ----------
         1          1
         2          2
         2          3
         4          4

7.符合更新条件，有更新记录，且符合删除条件，则删除

SQL> merge into tt1 using tt2 on(tt1.c1 = tt2.c3) when matched then update set t
t1.c2=tt2.c4 where tt1.c1 > 1 delete where tt1.c1=2 and tt2.c3=2 when not matche
d then insert values(tt2.c3, tt2.c4) where tt2.c3>5;

2 行已合并。

SQL> select * from tt1;

        C1         C2
---------- ----------
         1          1
         4          4