Re: question about merge

From: joel garry <joel-garry_at_home.com>
Date: Tue, 25 Oct 2011 16:27:57 -0700 (PDT)
Message-ID: <a3b377b5-0b65-462d-8317-d47467c031ec_at_p20g2000prm.googlegroups.com>



On Oct 25, 3:28 pm, ddf <orat..._at_msn.com> wrote:
> 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
>
> You need to reverse the matched and unmatched conditions:
>
> SQL> create table empm as select * from emp where deptno = 10;
>
> Table created.
>
> SQL>
> SQL>      merge into empm m
>   2       using emp e
>   3       on ( e.EMPNO = m.EMPNO )
>   4       when matched then
>   5           update set job='janitor' where ename='KING'
>   6           delete where job='janitor';
>
> 1 row merged.
>
> SQL>
> SQL> select * From empm;
>
>      EMPNO ENAME      JOB              MGR HIREDATE         SAL
> COMM     DEPTNO
> ---------- ---------- --------- ---------- --------- ----------
> ---------- ----------
>       7782 CLARK      MANAGER         7839 09-JUN-81
> 2450                    10
>       7934 MILLER     CLERK           7782 23-JAN-82
> 1300                    10
>
> SQL>
> SQL> rollback;
>
> Rollback complete.
>
> SQL>
> SQL>      merge into empm m
>   2       using emp e
>   3       on ( e.EMPNO = m.EMPNO )
>   4       when not matched then
>   5           insert (m.empno, m.ename, m.job)
>   6           values (e.empno, e.ename, e.job)
>   7           where e.deptno=20
>   8       when matched then
>   9           update set job='janitor' where ename='KING'
>  10           delete where job='janitor';
>
> 6 rows merged.
>
> SQL>
> SQL> select * From empm;
>
>      EMPNO ENAME      JOB              MGR HIREDATE         SAL
> COMM     DEPTNO
> ---------- ---------- --------- ---------- --------- ----------
> ---------- ----------
>       7782 CLARK      MANAGER         7839 09-JUN-81
> 2450                    10
>       7934 MILLER     CLERK           7782 23-JAN-82
> 1300                    10
>       7788 SCOTT      ANALYST
>       7566 JONES      MANAGER
>       7902 FORD       ANALYST
>       7369 SMITH      CLERK
>       7876 ADAMS      CLERK
>
> 7 rows selected.
>
> SQL>
>
> David Fitzjarrell

That's _got_ to be a bug! Why would the ordering of [matched v. not] matter? The docs imply matched first, and I know all the ones I've written have done it that way, but then again, I haven't used it to delete. Am I missing something? What if KING is in dept 20?

jg

--
_at_home.com is bogus.
“The system itself does work really well... It’s the interaction with
our customers we don’t have at the level we want to have it at.”
http://www.signonsandiego.com/news/2011/oct/24/water-billing-system-not-where-we-want-to-be/
Received on Tue Oct 25 2011 - 18:27:57 CDT

Original text of this message