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 🙂