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

In this post, I am going to talk about ORA-30926 error and how to solve it. Let’s create the following tables for our example.

CREATE TABLE TAB1
AS
SELECT * FROM USER_OBJECTS;
CREATE TABLE TAB2
AS
SELECT * FROM (
    SELECT OBJECT_ID FROM USER_OBJECTS
    UNION ALL
    SELECT OBJECT_ID FROM USER_OBJECTS
);

This error occurs due to duplicate records. As seen in the example below, the query gives ORA-30926 error. Because there are duplicate rows in the source query.

SQL> MERGE INTO TAB1 T
  2  USING
  3  (
  4      SELECT A.* FROM TAB1 A
  5      JOIN TAB2 B ON A.OBJECT_ID = B.OBJECT_ID
  6      WHERE OBJECT_TYPE = 'TABLE'
  7  ) SRC
  8  ON (T.OBJECT_ID = SRC.OBJECT_ID)
  9  WHEN MATCHED THEN
 10  UPDATE SET OBJECT_NAME = OBJECT_NAME||' copy';
MERGE INTO TAB1 T
           *
1 sat²r²nda HATA:
ORA-30926: kaynak tablolar²nda sabit bir sat²r k³mesi al²nam²yor

So, we can use DISTINCT keyword for workaround.

SQL> MERGE INTO TAB1 T
  2  USING
  3  (
  4      SELECT DISTINCT A.* FROM TAB1 A
  5      JOIN TAB2 B ON A.OBJECT_ID = B.OBJECT_ID
  6      WHERE OBJECT_TYPE = 'TABLE'
  7  ) SRC
  8  ON (T.OBJECT_ID = SRC.OBJECT_ID)
  9  WHEN MATCHED THEN
 10  UPDATE SET OBJECT_NAME = OBJECT_NAME||' copy';

412 sat²r birle■tirildi.

I hope it helps you 🙂

Posted in SQL
Leave a Reply

Your email address will not be published.