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

Merge statement can be used if you are trying to use transformed data fetched from a set of tables to update another table.

MERGE INTO table_3 c
USING (SELECT a.book_id, a.author_id
         FROM table_1 a, table_2 b
        WHERE a.author_id = b.author_id) src
ON (c.book_id = src.book_id)
WHEN MATCHED THEN
  UPDATE SET c.author_id  = src.author_id;

This mergae statement might return error ‘ORA-30926: unable to get a stable set of rows in the source tables’ , if there is duplicates in the ‘USING’ query. For example, duplicate book_id retuned by the ‘src’ query in the given example. This can be fixed by using a DISTINCT in the query

MERGE INTO table_3 c
USING (SELECT DISTINCT a.book_id, a.author_id
         FROM table_1 a, table_2 b
        WHERE a.author_id = b.author_id) src
ON (c.book_id = src.book_id)
WHEN MATCHED THEN
  UPDATE SET c.author_id  = src.author_id;

-Dhanya James

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

Leave a Reply

*