question about merge
Date: Tue, 25 Oct 2011 20:44:04 +0200
Message-ID: <j87026$k5l$1_at_news.task.gda.pl>
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/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