Ora-30926: Unable to Get a Stable Set of Rows in the Source Tables When Merging Tables

ORA-30926: unable to get a stable set of rows in the source tables

This is usually caused by duplicates in the query specified in USING clause. This probably means that TABLE_A is a parent table and the same ROWID is returned several times.

You could quickly solve the problem by using a DISTINCT in your query (in fact, if 'Y' is a constant value you don't even need to put it in the query).

Assuming your query is correct (don't know your tables) you could do something like this:

  MERGE INTO table_1 a
      USING 
      (SELECT distinct ta.ROWID row_id
              FROM table_1 a ,table_2 b ,table_3 c
              WHERE a.mbr = c.mbr
              AND b.head = c.head
              AND b.type_of_action <> '6') src
              ON ( a.ROWID = src.row_id )
  WHEN MATCHED THEN UPDATE SET in_correct = 'Y';

ORA-30926: unable to get a stable set of rows in the source tables in merge query in oracle

You must have single record for matching criteria in the USING clause.

so you can use the GROUP BY and MAX in USING clause as follows:

MERGE INTO BB_TST_HISTORY H
USING (
          SELECT MAX(R.AUSUEBUNGSBEZEICHNUNG) AS AUSUEBUNGSBEZEICHNUNG, 
                 R.ROOT, R.SECURITY_TYP, R.BOERSE
            FROM BB_TST_ROLLUP R
            JOIN (
              SELECT ROOT, BOERSE, SECURITY_TYP
                FROM BB_EXPORT_FILTER
              MINUS
              SELECT ROOT, BOERSE, SECURITY_TYP
                FROM BB_TST_EXCEPTION
          ) E
          ON R.ROOT = E.ROOT
             AND R.SECURITY_TYP = E.SECURITY_TYP
             AND R.BOERSE = E.BOERSE
         GROUP BY R.ROOT, R.SECURITY_TYP, R.BOERSE
      ) S ON ( S.ROOT = H.ROOT
   AND S.SECURITY_TYP = H.SECURITY_TYP AND S.BOERSE = H.BOERSE )
WHEN MATCHED THEN UPDATE
   SET H.AUSUEBUNGSBEZEICHNUNG = S.AUSUEBUNGSBEZEICHNUNG
WHEN NOT MATCHED THEN
INSERT VALUES ( S.AUSUEBUNGSBEZEICHNUNG );

ORA-30926: unable to get a stable set of rows in the source tables when running Merge Query

I have already used a partition by and where rn=1 in the using clause to pick up only the non-duplicate records from source, but Oracle still throws the error.

Your

ROW_NUMBER() OVER ( PARTITION BY PAY_RANGE_START_DATE_KEY,
      AA_PERSON_NATURAL_KEY, AA_PERSON_ASSIGNMENT_KEY, SCHEDULE_LINE_ID,
      SRC_CREATED_DATE, SRC_LAST_UPDATE_DATE ORDER BY ROWNUM ) AS rn

with a filter rn=1
removes duplicates by 6 columns, while you are using 4 columns in ON() clause.
Moreover you are using another filter to filter rows for update.

The easiest way to get what you want is to filter needed data in USING clause:

MERGE
/*+ parallel(A) enable_parallel_dml*/
INTO
(
  SELECT
    PAY_RANGE_START_DATE_KEY,
    AA_PERSON_NATURAL_KEY,
    AA_PERSON_ASSIGNMENT_KEY,
    SCHEDULE_LINE_ID,
    SRC_CREATED_DATE,
    SRC_LAST_UPDATE_DATE
  FROM
    EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP
)
A USING
(
SELECT
  PAY_RANGE_START_DATE_KEY,
  AA_PERSON_NATURAL_KEY,
  AA_PERSON_ASSIGNMENT_KEY,
  SCHEDULE_LINE_ID,
  SRC_CREATED_DATE,
  SRC_LAST_UPDATE_DATE
FROM
  (
    SELECT
      BB.PAY_RANGE_START_DATE_KEY,
      BB.AA_PERSON_NATURAL_KEY,
      BB.AA_PERSON_ASSIGNMENT_KEY,
      BB.SCHEDULE_LINE_ID,
      BB.SRC_CREATED_DATE,
      BB.SRC_LAST_UPDATE_DATE,
      ROW_NUMBER() OVER ( 
         PARTITION BY BB.PAY_RANGE_START_DATE_KEY,
                      BB.AA_PERSON_NATURAL_KEY, 
                      BB.AA_PERSON_ASSIGNMENT_KEY, 
                      BB.SCHEDULE_LINE_ID
         ORDER BY ROWNUM /*?*/ 
         ) AS rn
    FROM
      EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP_FRS_356 BB
     ,EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP AA
    WHERE
      AA.PAY_RANGE_START_DATE_KEY = BB.PAY_RANGE_START_DATE_KEY AND
      AA.AA_PERSON_NATURAL_KEY    = BB.AA_PERSON_NATURAL_KEY AND
      AA.AA_PERSON_ASSIGNMENT_KEY = BB.AA_PERSON_ASSIGNMENT_KEY AND
      AA.SCHEDULE_LINE_ID         = BB.SCHEDULE_LINE_ID
      AND (
          AA.SRC_CREATED_DATE     <> BB.SRC_CREATED_DATE
       OR AA.SRC_LAST_UPDATE_DATE <> BB.SRC_LAST_UPDATE_DATE
      )
  )
WHERE
  rn = 1
)
B ON
(
  A.PAY_RANGE_START_DATE_KEY = B.PAY_RANGE_START_DATE_KEY AND
  A.AA_PERSON_NATURAL_KEY    = B.AA_PERSON_NATURAL_KEY AND
  A.AA_PERSON_ASSIGNMENT_KEY = B.AA_PERSON_ASSIGNMENT_KEY AND
  A.SCHEDULE_LINE_ID         = B.SCHEDULE_LINE_ID
)
WHEN MATCHED THEN
  UPDATE
  SET
    A.SRC_CREATED_DATE     = B.SRC_CREATED_DATE,
    A.SRC_LAST_UPDATE_DATE = B.SRC_LAST_UPDATE_DATE;

As you can see I filtered rows you don't need in the USING clause, so you don't need WHERE clause in UPDATE SET and removed duplicates by 4 columns used for matching

ORA-30926: unable to get stable rowset in source tables

Your problem - the using subquery produces duplicated rows in the key nombre_contribuyente

Check with

with v_using as (SELECT  DISTINCT 
                    archivo.id_archivo,archivo.rfc, archivo.nombre_contribuyente , archivo.situacion_contribuyente ,
                    archivo.oficio_global, archivo.publicacion_presuntos , archivo.publicacion_definitivos
    FROM B69_CAT_ALERTA_ARCHIVO archivo
    INNER JOIN  TBL_TRANSACCIONES txn   
       ON txn.titular = archivo.nombre_contribuyente)
select nombre_contribuyente, count(*)
from v_using
group by nombre_contribuyente
order by 2 desc;

On the top of the result you see the keys that have count > 1

You must reformulated the using subquery such that the join key of merge is unique - Distinct is not enough, you must use e.g. GROUP BY on the key.

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

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.

Merge into gives error ORA-30926: unable to get a stable set of rows in the source tables

From the comments to the question it becomes clear that the author wants to update the same record many times.

Of course, this cannot get past ORA-30926 when trying to do it by a merge construct.

It's hard or impossible to do such a thing in pure oracle sql, but it's easily done with a pl/sql function.

For example:

create or replace function replace_chars(p_str varchar2, p_id number, p_kind varchar2) return varchar2 as
                                l_str varchar2(32767):=p_str;
begin
    for u in (select u.position, u.character  from sdc_diakrietposities_cip u 
                  where u.anumber=p_id and u.kind=p_kind order by u.position) loop
        if (u.position >= 1 or u.position <= length(l_str)) then
            l_str:=substr(l_str, 1, u.position-1)|| u.character || substr(l_str, u.position+1);
        end if;
    end loop;
    return l_str;
end;

Use like this:

update sdc_compare_person t 
set t.GESNAM_D= replace_chars(t.GESNAM_D, t.sourcekey, 'Lastname');

I'd suggest backing up your table before running this.

ORA-30926: unable to get a stable set of rows in the source tables when Merging tables

It's a bit tricky case. The primary reason is that you seems have duplicates in TMP_DP_REGIAO.DS_PROTHEUS_CODE column and MERGE tries to update the same row of destination table several times. But if new values and old values in updated columns are the same, Oracle can skip this issue of duplicates:

SQL> select * from t;

      CODE TEXT                                                                 
---------- ----------                                                           
         1 test                                                                 

SQL> merge into t using (
  2   select 1 code,'test' text from dual union all
  3   select 1 code,'test' text from dual
  4  ) s
  5  on (t.code = s.code)
  6  when matched then
  7    update set t.text = s.text
  8  /

2 rows merged 

But if old and new values are different Oracle raises the exception you get:

SQL> merge into t using (
  2   select 1 code,'a' text from dual union all
  3   select 1 code,'a' text from dual
  4  ) s
  5  on (t.code = s.code)
  6  when matched then
  7    update set t.text = s.text
  8  /
merge into t using (
           *
error in line 1:
ORA-30926: unable to get a stable set of rows in the source tables 

What is a good workaround for this error? ORA-30926: unable to get a stable set of rows in the source tables

Since you select more columns than only the columns used for matching, the DISTINCT clause cannot guarantee that the matching will yield unique matching columns.

Try to define a UNIQUE INDEX or UNIQUE CONSTRAINT on the columns "FROM", "TO" and "TIMESTAMP_" in table "IN_DATA", if there is not already a such an index or constraint or a primary key.

Merge query returning ORA-30926: unable to get a stable set of rows in the source tables

if you see i used distinct in both of the queries so duplicating of
rows is not an issue.

You probably have duplicates in data. DISTINCT does not guarantee you have IdToUpdate unique when you use it with other columns. See:

CREATE TABLE #MyTable(IdToUpdate INT, LogSetIdToUpdateTo INT);

INSERT INTO #MyTable VALUES (1,1), (1,2), (2,1),(3,1);

SELECT DISTINCT IdToUpdate, LogSetIdToUpdateTo
FROM #MyTable;

LiveDemo

You will get IdToUpdate twice. Check your data:

with cte AS (
  select distinct nullLogSetId.Id as IdToUpdate,
                  knownLogSetId.LogSetId LogSetIdToUpdateTo
  from MyTable knownLogSetId 
  join MyTable nullLogSetId
    on knownLogSetId.IdentifierType = nullLogSetId.IdentifierType 
   and knownLogSetId.Identifier = nullLogSetId.Identifier 
  where 
    knownLogSetId.IdentifierType = 'DEF'
    and knownLogSetId.LogSetId >= 0 
    and nullLogSetId.LogSetId = -1
)
SELECT IdToUpdate, COUNT(*) AS c
FROM cte
GROUP BY IdToUpdate
HAVING COUNT(*) > 1;

One way to go is to use aggregation function(MAX/MIN) instead of DISTINCT:

merge into MyTable
using
(

  select nullLogSetId.Id as IdToUpdate,
         MAX(knownLogSetId.LogSetId) AS LogSetIdToUpdateTo 
  from MyTable knownLogSetId 
  join MyTable nullLogSetId
    on knownLogSetId.IdentifierType = nullLogSetId.IdentifierType 
   and knownLogSetId.Identifier = nullLogSetId.Identifier 
  where 
    knownLogSetId.IdentifierType = 'DEF'
    and knownLogSetId.LogSetId >= 0 
    and nullLogSetId.LogSetId = -1
  GROUP BY nullLogSetId.Id
) on (Id = IdToUpdate)
when matched then
update set LogSetId = LogSetIdToUpdateTo

---

Related Topics