Reputation: 445
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
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
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