Re: question about merge

From: ddf <oratune_at_msn.com>
Date: Tue, 25 Oct 2011 16:36:49 -0700 (PDT)
Message-ID: <609c3878-ef61-4eb8-9142-66aaf57e616a_at_gk10g2000vbb.googlegroups.com>



On Oct 25, 4:27 pm, joel garry <joel-ga..._at_home.com> wrote:
> 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-n...- Hide quoted text -
>
> - Show quoted text -

All I had time to do is get it to work by following the docs (they say you have have the pieces in either order). I haven't dug deep into the bowels of it to see why one works and another doesn't.

It probably worked because Lindsay Lohan will be posing for Playboy sometime soon.

David Fitzjarrell Received on Tue Oct 25 2011 - 18:36:49 CDT

Original text of this message