question about merge

create table empm as select * from emp where deptno = 10;

then I merge more data this way:

     merge into empm m
     using emp e
     on ( e.EMPNO = m.EMPNO )
     when matched then
         update set job='janitor' where ename='KING'
         delete where job='janitor';

I check the result and 'KING the Janitor' is gone from empm table (as I expected)

      EMPNO ENAME JOB
---------- ---------- -------

       7782 CLARK      MANAGER
       7934 MILLER     CLERK

I rollback and make another merge, this time like this:

     merge into empm m
     using emp e
     on ( e.EMPNO = m.EMPNO )
     when matched then
         update set job='janitor' where ename='KING'
         delete where job='janitor'
     when not matched then
         insert (m.empno, m.ename, m.job)
         values (e.empno, e.ename, e.job)
         where e.deptno=20;

I check the result and see that 'KING the Janitor' is still there. I expected the same behaviour as previously plus some guys from department 20.

      EMPNO ENAME JOB
---------- ---------- ---------

       7782 CLARK      MANAGER
       7839 KING       janitor
       7934 MILLER     CLERK
       7788 SCOTT      ANALYST
       7566 JONES      MANAGER
       7902 FORD       ANALYST
       7369 SMITH      CLERK
       7876 ADAMS      CLERK

I checked out the manual for merge:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_9016.htm

but I don't see any reason for not removing janitor in the second example. what's all about these additional inserts (or when not matched clause) that makes oracle not to remove King the Janitor from the result set in the second example?

I would greatly appreciate your explanation. I set Followup-To misc group for this post so your answers will be directed there.

thank you,
geos Received on Tue Oct 25 2011 - 13:44:04 CDT