Re: question about merge

From: joel garry <joel-garry_at_home.com>
Date: Tue, 25 Oct 2011 16:19:19 -0700 (PDT)
Message-ID: <3f50f8df-4b22-494a-af20-0a02d6e476c5_at_h23g2000pra.googlegroups.com>



On Oct 25, 11:44 am, geos <g..._at_nowhere.invalid> wrote:
> 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/statem...
>
> 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

I have no idea, but I see the same thing on 10.2.0.4. I can only guess that it is a bug.

Dan has a different opinion about merge: http://www.morganslibrary.com/reference/merge.html

I don't like merge because it is fragile, you have to be exceedingly careful to handle errors, else it will all go down in flames with some little bad data. But I would like to know the answer to your observation. It's almost as though adding the insert created a most un-set-like ordering of update and delete, so it is not finding the updated row to delete.

jg

--
_at_home.com is bogus.
http://www.tmcnet.com/channels/call-monitoring/articles/233658-motive-oracle-15-billion-purchase-rightnow-speculated.htm
Received on Tue Oct 25 2011 - 18:19:19 CDT

Original text of this message