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 -
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