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/B...ments_9016.htm

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

On Oct 25, 11:44*am, geos <g... (AT) nowhere (DOT) invalid> wrote:
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

On Oct 25, 11:44*am, geos <g... (AT) nowhere (DOT) invalid> wrote:
I have no idea, but I see the same thing on 10.2.0.4. I can only
guess that it is a bug.

Dan has a different opinion about merge: http://www.morganslibrary.com/reference/merge.html

I don't like merge because it is fragile, you have to be exceedingly
careful to handle errors, else it will all go down in flames with some
little bad data. But I would like to know the answer to your
observation. It's almost as though adding the insert created a most
un-set-like ordering of update and delete, so it is not finding the
updated row to delete.

jg
--
@home.com is bogus.
http://www.tmcnet.com/channels/call-...speculated.htm

On Oct 25, 3:28*pm, ddf <orat... (AT) msn (DOT) com> wrote:
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
--
@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/2...we-want-to-be/

On Oct 25, 4:27*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:
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

ddf wrote:
guys, thanks for your opinions. if I use 'no-doubt-syntax' like this:

...
when matched then
update set m.job='janitor' where m.ename='KING'
delete where m.job='janitor'
...

it works as expected (10.2.0.1.0). I understand that reversing order
helps, adding aliases helps too, but this behaviour looks suspicious to
me anyway.

thank you,
geos