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