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 🙂