My oracle merge does not work

Question

I am running Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod. I want to merge data from m2 into m1. I expect to see 3 records in m1 after the merge, one for 'c' with a knt of 4, one for 'a' with a knt of 1 and one for 'b' with a knt of 1.

But I get everything. As if no checking for update or insert occurred.

See below.

Best regards,

Phil

 SQL> desc m1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 K                                                  VARCHAR2(6)
 V                                                  VARCHAR2(6)
 KNT                                                NUMBER(4)

SQL> desc m2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 K                                                  VARCHAR2(6)
 V                                                  VARCHAR2(6)
 KNT                                                NUMBER(4)

SQL> select * from m1;

no rows selected

SQL> select * from m2;

K      V             KNT
------ ------ ----------
a      aaa             0
b      bbb             0
c      ccc             0
c      ccc             0
c      ccc             0
a      aaa             0
b      bbb             0
c      ccc             0
c      ccc             0


SQL> merge into m1 d
  2  using (select k,v,knt from m2) s

 SQL> desc m1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 K                                                  VARCHAR2(6)
 V                                                  VARCHAR2(6)
 KNT                                                NUMBER(4)

SQL> desc m2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 K                                                  VARCHAR2(6)
 V                                                  VARCHAR2(6)
 KNT                                                NUMBER(4)

SQL> select * from m1;

no rows selected

SQL> select * from m2;

K      V             KNT
------ ------ ----------
a      aaa             0
b      bbb             0
c      ccc             0
c      ccc             0
c      ccc             0
a      aaa             0
b      bbb             0
c      ccc             0
c      ccc             0


SQL> merge into m1 d
  2  using (select k,v,knt from m2) s
  3  on (d.k = s.k)
  4  when matched then
  5  update set d.knt = d.knt+1
  6  when not matched then
  7  insert(d.k,d.v,d.knt)
  8  values(s.k,s.v,s.knt)
  9  ;

SQL> select * from m1;

K      V             KNT
------ ------ ----------
b      bbb             0
b      bbb             0
c      ccc             0
c      ccc             0
c      ccc             0
c      ccc             0
c      ccc             0
a      aaa             0
a      aaa             0

score 3 · Answer 1 · answered Nov 04 '11 at 17:05

3

Are you sure you want a MERGE? It sounds like you really want

INSERT INTO m2( k, v, knt )
  SELECT k, v, count(*)
    FROM m1
   GROUP BY k, v

A MERGE is a set-based operation. The data in M2 is evaluated at the time the query is executed so your USING clause is not going to see the rows that are being inserted as part of the MERGE. Since the USING clause returns 0 rows, all the data from M1 is going to be inserted into M2. The WHEN MATCHED clause is never going to be triggered.

answered Nov 04 '11 at 17:05

Justin Cave

227,342
24
367
384

Aha !!! Post at [http://stackoverflow.com/questions/2337271/ora-30926-unable-to-get-a-stable-set-of-rows-in-the-source-tables] indicates that I have to have distinct records to insert. Thus -- if I change my sql to --- arrggh see my answer to my own question – Phillip Neal Nov 07 '11 at 16:53

score 1 · Answer 2 · answered Nov 07 '11 at 17:02

Sigh. It looks like I have to keep the records I am inserting 'distinct'.

So this works. .... kind of.

 merge into m1 d
 using (select distinct k,v,knt from m2) s
 on (d.k = s.k and d.v = s.v)
 when matched then
 update set d.knt = d.knt+1
 when not matched then
 insert(d.k,d.v,d.knt)
 values(s.k,s.v,s.knt)

My oracle merge does not work

2 Answers2