ORA-30926: Get Stable Rows in Source Tables

Tagged with: Databases & Architecture, Software Development, Technical Tips & FAQs

A merge statement can be used if you try 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 merge statement might return the error ‘ORA-30926: unable to get a stable set of rows in the source tables’ if there are duplicates in the ‘USING’ query. For example, duplicate book_id returned 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;

Contact Unidev for more information.