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

Omnipresent picture Omnipresent · Feb 25, 2010 · Viewed 429.5k times · Source

I am getting

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

in the following query:

  MERGE INTO table_1 a
      USING 
      (SELECT a.ROWID row_id, 'Y'
              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';

I've ran table_1 it has data and also I've ran the inside query (src) which also has data.

Why would this error come and how can it be resolved?

Answer

Pop picture Pop · Feb 25, 2010

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';