CODEWITHSUNDEEP
oracle-databaseinsertmergeupsert
Phillip Neal
Phillip Neal

Reputation: 445

My oracle merge does not work

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

Upvotes: 0

Views: 1769

Answers (2)

Phillip Neal
Phillip Neal

Reputation: 445

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)

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231881

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.

Upvotes: 3

Related Questions