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
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.htmReceived on Tue Oct 25 2011 - 18:19:19 CDT