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 🙂
Question :
1) Doesnt the DISTINCT really give 2 or more rows ??
2) Also I cant make out what the result is as its in non-english language ??
In the example, DISTINCT can be used to deduplicate rows when JOIN the datasets.
As for your second question, in the first example it gives an error message and in the second one it is written 412 rows merged.