Merge and source table multiple matches

Question

I face a problem with the following query:

merge into table2 d
using (
  select firstname, lastname, max(id) id
  from table1 t1
  group by firstname, lastname
  having count(0) = 1
) s
on (d.firstname=s.firstname and d.lastname=s.lastname)
when matched then update set t1_id = s.id;

If multiple rows in table2 match the ON clause, then I get "SQL Error: ORA-30926: unable to get a stable set of rows in the source tables"

Do you know any way to filter and just ignore those "duplicates"? Thanks.

EDIT

@Polppan, your request for sample data has lead me on a very strange way:

here some sample data:

table1
ID       firstname     lastname
1        John          Doe
2        John          DOE
3        Jane          Doe
4        Jane          Doe

(notice the UPPER)

table2
t1_ID    firstname     lastname
null     John          Doe
null     Jane          Doe
null     Jane          Doe

now, I couldn't reproduce the error with those data until:

the ON clause is "UPPER(d.firstname)=UPPER(s.firstname) AND UPPER(d.lastname)=UPPER(s.lastname)" (which is what I have since I need case-insensitive matching)
one of the lines in table1 has DOE in uppercase

Any idea why?

Jacob · Answer 1 · 2013-09-23T17:48:01.420

1

Try using DISTINCT

MERGE INTO   table2 d
      USING  (SELECT     DISTINCT ((firstname))fname, ((lastname))lname,max(id) id
                        FROM     table1 t1
                        GROUP BY   firstname, lastname
                     HAVING  COUNT (0) = 1
                  ) s
          ON     ( upper(d.firstname) = upper(fname)
                  AND upper(lastname) = upper(lname))
WHEN MATCHED
THEN
    UPDATE SET id = s.id;

Update 1

MERGE INTO   table2 d
      USING  (SELECT     DISTINCT upper((firstname))fname, upper((lastname))lname,max(id) id
                        FROM     table1 t1
                        GROUP BY   firstname, lastname
                     HAVING  COUNT (0) = 1
                  ) s
          ON     ( upper(d.firstname) = upper(fname)
                  AND upper(lastname) = upper(lname))
WHEN MATCHED
THEN
    UPDATE SET id = s.id;

edited Sep 23 '13 at 17:48

answered Sep 23 '13 at 13:18

Jacob

14,463
65
207
320

2

I hope you don't use `count(0)` because you think it is faster than `count(*)` – Sep 23 '13 at 13:20
I already use DISTINCT but with the same result. Actually I think the issue is in the ON clause (initial post edited). (and I use count(0) because the snippet I copied used that ;-) ) – bertrand Sep 23 '13 at 13:22
@bertrand Can you provide sample data? – Jacob Sep 23 '13 at 13:23
@bertrand I have modified my answer, included id as well in ON clause. Presume that id is primary key in your source and target tables. If id is not primary key then use whatever primary key you have. – Jacob Sep 23 '13 at 13:25
@Polppan I edited the initial post with sample data and something I can't explain regarding case sensitivity – bertrand Sep 23 '13 at 13:53
@bertrand Have you seen my modified answer with ID as well in ON clause? Use that along with either convert data to upper or lower case. And let me know whether you still have duplicate rows. – Jacob Sep 23 '13 at 13:57
@Polppan I don't have an ID to match. the whole point of the query is to populate table2 with table1's IDs (if I have a unique fn&ln match) – bertrand Sep 23 '13 at 13:59
@bertrand `t1_ID` in table2 is `null` or empty? – Jacob Sep 23 '13 at 14:03
@Polppan I'm not sure since sqldeveloper displays (null) even if I "udpate table2 set t1_id='';". But since this is the destination column, I don't think it causes my trouble – bertrand Sep 23 '13 at 14:16
@bertrand I have modified my answer, have a look and see whether this helps. – Jacob Sep 23 '13 at 14:54
@Polppan your query works but compares case-insensitively (and when I add upper to the select, I get the same error back) – bertrand Sep 23 '13 at 16:56
@bertrand Where did you exactly add upper? – Jacob Sep 23 '13 at 17:10
@Polppan either in the select clause (SELECT UPPER(firstname) fname, etc.) or directly in the ON clause => same result. – bertrand Sep 23 '13 at 17:13
@bertrand I have added another sql as update 1 with upper in select statement, I do not get any errors with your given data. – Jacob Sep 23 '13 at 17:27
@Polppan your second = tries to connect fname and lname. maybe that's why it works? ;-) – bertrand Sep 23 '13 at 17:31
@bertrand Have a look at this http://stackoverflow.com/a/2337326/599528 and read this as well http://aykutakin.wordpress.com/2013/01/29/ora-30926-unable-to-get-a-stable-set-of-rows-in-the-source-tables/ – Jacob Sep 23 '13 at 17:47
@Polppan yes those links tells me what I fear : I must have an ON clause which will return only unique matches. But my use case and the data I work with does and will always contain multiple matches... – bertrand Sep 23 '13 at 20:59
@bertrand If you have multiple matches or duplicate records then I do not think it is quite possible to use `MERGE` unless there are some alternate ways. – Jacob Sep 24 '13 at 04:41

Merge and source table multiple matches

1 Answers1