I prepare a table as follows:

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/B...ments_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