Re: question about merge

From: ddf <oratune_at_msn.com>
Date: Tue, 25 Oct 2011 15:28:57 -0700 (PDT)
Message-ID: <b1e83668-372e-4f1f-a1c0-66074e38c5b9_at_gk10g2000vbb.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

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 Received on Tue Oct 25 2011 - 17:28:57 CDT