关于merge into 的一点研究

Question

最近使用 merge into 语句时发现，update，delete之间存在一些微妙的关系，特将结论和测试过程列举如下：
测试环境：oracle11g
结论：在merge into中，当update子句中存在where clause时，只有满足on和update中的where双重条件时，才会执行delete 子句。--创建测试表，并插入数据
CREATE TABLE t1 (  
"ID" NUMBER(10) NOT NULL ,  
"NAME" VARCHAR2(255 BYTE) NULL ,  
"AGE" NUMBER(10) NULL ,  
"SE" VARCHAR2(255 BYTE) NULL   
)  
;  
INSERT INTO t1 VALUES ('1201', 'Peter', '23', 'male');  
INSERT INTO t1 VALUES ('6695', 'Lu', '12', 'female');  
INSERT INTO t1 VALUES ('6720', 'Ka', '22', 'female');  
   
CREATE TABLE t2 (  
"ID" NUMBER NULL ,  
"NAME" VARCHAR2(255 BYTE) NULL ,  
"AGE" NUMBER NULL ,  
"SE" VARCHAR2(255 BYTE) NULL   
)  
;  
INSERT INTO t2 VALUES ('1201', 'John', '47', 'male');
INSERT INTO t2 VALUES ('1202', 'lili', '30', 'female'); 
--结果0
SQL> select * from t1;        ID NAME                        AGE SE
---------- -------------------- ---------- ---------
      1201 Peter                        23 male
      6695 Lu                           12 female
      6720 Ka                           22 female
SQL> select * from t2;        ID NAME                        AGE SE
---------- -------------------- ---------- --------------------
      1201 John                         47 male
      1202 lili                         30 female
--测试1
MERGE INTO t1 b  
USING t2 e  
ON (b.ID = e.ID)  
WHEN MATCHED THEN  
update    
  set b.NAME = E.NAME,  
  b.AGE = E.AGE,  
  b.SE = E.SE  
    where  t1.name<>'Peter'
delete  where (t1.name = 'Peter')   
WHEN NOT MATCHED THEN  
insert  (b.ID,b.NAME,b.AGE,b.SE)  
values(E.ID,E.NAME,E.AGE,E.SE) ;  --结果1
--创建测试表，并插入数据
CREATE TABLE t1 (  
"ID" NUMBER(10) NOT NULL ,  
"NAME" VARCHAR2(255 BYTE) NULL ,  
"AGE" NUMBER(10) NULL ,  
"SE" VARCHAR2(255 BYTE) NULL   
)  
;  
INSERT INTO t1 VALUES ('1201', 'Peter', '23', 'male');  
INSERT INTO t1 VALUES ('6695', 'Lu', '12', 'female');  
INSERT INTO t1 VALUES ('6720', 'Ka', '22', 'female');  
   CREATE TABLE t2 (  
"ID" NUMBER NULL ,  
"NAME" VARCHAR2(255 BYTE) NULL ,  
"AGE" NUMBER NULL ,  
"SE" VARCHAR2(255 BYTE) NULL   
)  
;  
INSERT INTO t2 VALUES ('1201', 'John', '47', 'male');
INSERT INTO t2 VALUES ('1202', 'lili', '30', 'female'); --结果
SQL> select * from t1;        ID NAME                        AGE SE
---------- -------------------- ---------- ---------
      1201 Peter                        23 male
      6695 Lu                           12 female
      6720 Ka                           22 female
SQL> select * from t2;        ID NAME                        AGE SE
---------- -------------------- ---------- --------------------
      1201 John                         47 male
      1202 lili                         30 female
--测试1
MERGE INTO t1 b  
USING t2 e  
ON (b.ID = e.ID)  
WHEN MATCHED THEN  
update    
  set b.NAME = E.NAME,  
  b.AGE = E.AGE,  
  b.SE = E.SE  
    where  t1.name<>'Peter'
delete  where (t1.name = 'Peter')   
WHEN NOT MATCHED THEN  
insert  (b.ID,b.NAME,b.AGE,b.SE)  
values(E.ID,E.NAME,E.AGE,E.SE) ;  --结果1
SQL> select * from t1;        ID NAME                        AGE SE
---------- -------------------- ---------- --------------------
      1201 Peter                        23 male
      6695 Lu                           12 female
      6720 Ka                           22 female
      1202 lili                         30 female可见，通过上述代码，只是将 t2 中 id 为 1202 的记录insert到了t1中，并没有将t1中name为Peter的记录删除，即delete语句没有执行。
rollback 回退结果。
现在表t1中内容为：
SQL> select * from t1;        ID NAME                        AGE SE
---------- -------------------- ---------- --------------------
      1201 Peter                        23 male
      6695 Lu                           12 female
      6720 Ka                           22 female
  
--测试2：
MERGE INTO t1 b  
USING t2 e  
ON (b.ID = e.ID)  
WHEN MATCHED THEN  
update    
  set b.NAME = E.NAME,  
  b.AGE = E.AGE,  
  b.SE = E.SE  
    where  t1.name='Peter'
delete  where (t1.name = 'Peter')   
WHEN NOT MATCHED THEN  
insert  (b.ID,b.NAME,b.AGE,b.SE)  
values(E.ID,E.NAME,E.AGE,E.SE) ;  --结果2：
SQL> select * from t1;        ID NAME                        AGE SE
---------- -------------------- ---------- --------------------
      1201 John                         47 male
      6695 Lu                           12 female
      6720 Ka                           22 female
      1202 lili                         30 female因为先执行了update，将t1中id为1201中的name修改为了John，所以不满足delete条件，delete不执行。从该测试中可以知道，当满足on条件时，先执行update后执行delete。同样rollback，
SQL> select * from t1;        ID NAME                        AGE SE
---------- -------------------- ---------- -------------------
      6695 Lu                           12 female
      6720 Ka                           22 female
      1201 Peter                        23 male--测试3：
MERGE INTO t1 b  
USING t2 e  
ON (b.ID = e.ID)  
WHEN MATCHED THEN  
update    
  set b.NAME = E.NAME,  
  b.AGE = E.AGE,  
  b.SE = E.SE  
    where  b.id='1201'
delete  where (b.id = '1201')   
WHEN NOT MATCHED THEN  
insert  (b.ID,b.NAME,b.AGE,b.SE)  
values(E.ID,E.NAME,E.AGE,E.SE) ; 

--结果3：   
SQL> select * from t1;        ID NAME                        AGE SE
---------- -------------------- ---------- --------------------
      6695 Lu                           12 female
      6720 Ka                           22 female
      1202 lili                         30 female   
  
当既满足on条件又满足update中的where条件时，delete语句执行了。   
  
  
为了确保万一，我们验证一下不满足update中where条件的情况：   
--测试4：
MERGE INTO t1 b  
USING t2 e  
ON (b.ID = e.ID)  
WHEN MATCHED THEN  
update    
  set b.NAME = E.NAME,  
  b.AGE = E.AGE,  
  b.SE = E.SE  
    where  b.id<>'1201'
delete  where (b.name = 'Peter')   
WHEN NOT MATCHED THEN  
insert  (b.ID,b.NAME,b.AGE,b.SE)  
values(E.ID,E.NAME,E.AGE,E.SE) ;  

--结果4：
SQL> select * from t1;        ID NAME                        AGE SE
---------- -------------------- ---------- --------------------
      6695 Lu                           12 female
      6720 Ka                           22 female
      1201 Peter                        23 male
      1202 lili                         30 female如上，对于1201记录，虽然既满足on条件，又满足delete中的where条件，但是因为不满足update的where条件，所以delete并没有执行。综上：在merge into中，当update子句中存在where clause时，只有满足on和update中的where双重条件时，才会执行delete 子句。
如有疏漏的地方，欢迎大家指正~

Answer 1

我想也是这样，on是匹配大条件，只有on中条件满足了入选，在此条件下，如果有where子句，再来看是否满足。

Answer 2

主要是用来验证update 和 delete 之间的关系