Oracle 11g - MERGE and error ORA-30926: unable to get a stable set of rows in the source tables

Question

I have read lots of posts related to the Oracle (11g) error ORA-30926, and I've checked Oracle's documentation on the proper use of the merge statement.

Based on previous threads, I've changed my code to specify a distinct value in the using clause and that is the value I compare in the ON clause. But I still get the ORA-30926 error.

I've also tested the subquery in the USING clause and it returns data without any problem. I've created a temporary table containing only data that meets conditions in the WHERE clause of the USING statement and tried to run that and I still get the error. Both tables have data in them also.

I hope someone can spot something in my code that is incorrect or give me any recommendations on testing.

BEGIN
MERGE 
INTO persons myTarget
USING (
    select 
      distinct(USERID),
      GIVENNAME,
      INITIALS,
      SN,
      GENERATIONQUALIFIER,
      TITLE,
      DISPLAYNAME,
      TELEPHONENUMBER,
      FACSIMILETELEPHONENUMBER,
      MOBILE,
      OTHERTELEPHONE
    from person_updates
    WHERE
      SN IS NOT NULL
      AND LENGTH(SN) < 20
      AND SUBSTR(USERID,0,2) IN (SELECT PLACEID FROM code_table)
      AND (LENGTH(USERID) = 8 OR LENGTH(USERID) = 10)
  ) mySource
  ON (myTarget.userid = mySource.USERID)
WHEN MATCHED THEN
    UPDATE SET myTarget.first_name = UPPER(mySource.GIVENNAME),
    myTarget.last_name = UPPER(mySource.SN),
    myTarget.generation = UPPER(mySource.GENERATIONQUALIFIER),
    myTarget.title = UPPER(mySource.TITLE),
    myTarget.display_name = UPPER(mySource.DISPLAYNAME),
    myTarget.phone_num = UPPER(mySource.TELEPHONENUMBER),
    myTarget.fax_num = UPPER(mySource.FACSIMILETELEPHONENUMBER),
    myTarget.mobile_num = UPPER(mySource.MOBILE),
    myTarget.dsn_phone = UPPER(mySource.OTHERTELEPHONE);
END;

Basically this question is duplicate of this question: [merge update oracle unable to get a stable set of rows](https://stackoverflow.com/questions/47659844/merge-update-oracle-unable-to-get-a-stable-set-of-rows/47662275#47662275) Run `SELECT userid FROM persons JOIN (a-subquery-from-USING-clause) USING userid GROUP BY userid HAVING COUNT(*) > 1` and you will get duplicate `userids` which cause this error. — krokodilko, Aug 06 '18 at 17:47
I thought my distinct(userid) in the USING clause would remove duplicates. I know that there are duplicate userid's in the person_updates table that is why I used the distinct(userid). Am I misunderstanding this? — M_66, Aug 06 '18 at 17:58
The "select distinct ..." query returns rows unique across all the column combinations. If the same user has multiple mobile numbers for example, then the same userid will be across the 2 rows but still the across all the column combination this is a unique set. — shrek, Aug 06 '18 at 18:13
Please read this question https://stackoverflow.com/questions/6127338/sql-mysql-select-distinct-unique-but-return-all-columns/6127471 to learn how to make workaround for `distinct` using analytic function ROW-NUMBER and a subquery — krokodilko, Aug 06 '18 at 18:39

Ankit Mongia · Accepted Answer · 2018-08-06T19:28:46.100

As correctly told by @shrek using distinct will give you distinct rows across combination of all the columns you have selected. I have used row_number analytical function to get distinct rows only based on userid.

Query:

BEGIN
MERGE 
INTO persons myTarget
USING (
    select * from(
    select 
      row_number() over(partition by userid order by null) as rn,
      USERID,
      GIVENNAME,
      INITIALS,
      SN,
      GENERATIONQUALIFIER,
      TITLE,
      DISPLAYNAME,
      EMPLOYEETYPE,
      TELEPHONENUMBER,
      FACSIMILETELEPHONENUMBER,
      MOBILE,
      OTHERTELEPHONE
    from person_updates
    WHERE
      SN IS NOT NULL
      AND LENGTH(SN) < 20
      AND SUBSTR(USERID,0,2) IN (SELECT PLACEID FROM code_table)
      AND (LENGTH(USERID) = 8 OR LENGTH(USERID) = 10)) where rn = 1
  ) mySource
  ON (myTarget.userid = mySource.USERID)
WHEN MATCHED THEN
    UPDATE SET myTarget.first_name = UPPER(mySource.GIVENNAME),
    myTarget.last_name = UPPER(mySource.SN),
    myTarget.generation = UPPER(mySource.GENERATIONQUALIFIER),
    myTarget.title = UPPER(mySource.TITLE),
    myTarget.display_name = UPPER(mySource.DISPLAYNAME),
    myTarget.dod_emp_type = UPPER(mySource.EMPLOYEETYPE),
    myTarget.phone_num = UPPER(mySource.TELEPHONENUMBER),
    myTarget.fax_num = UPPER(mySource.FACSIMILETELEPHONENUMBER),
    myTarget.mobile_num = UPPER(mySource.MOBILE),
    myTarget.dsn_phone = UPPER(mySource.OTHERTELEPHONE);
END;

Hope this will help.

Oracle 11g - MERGE and error ORA-30926: unable to get a stable set of rows in the source tables

1 Answers1