Q

Does Oracle ignore duplicate rows found in subqueries?

My question is when I do an INSERT query having a subquery that collects data from another table, what happen if

one of inserted records found has a duplicate in the inserted table? Will Oracle ignore the duplicate record and just insert the new ones, or stop the query and return SQLCODE as duplicate record? Example:

 INSERT INTO TAB1
    SELECT COLUMN1,
           COLUMN2,
           'N',
           COLUMN3
      FROM  TAB2
     WHERE  COLUMN1 = 'A'
 
 COLUMN1 and COLUMN2 is PK of TAB1.
The subquery could return multiple rows which will be inserted automatically into TAB1. I use this query to avoid using the cursor to fetch and insert for each record. So, I'm concerned about how Oracle reacts when it finds the duplicate. It will be perfect if it's able to ignore the duplicate records and just add in the new ones, then send an SQLCODE 'duplicate key' to notice that at least one row was found duplicate.

If a duplicate row is found as determined by the Primary Key constraint, then the INSERT statement will fail and an error message will be displayed saying that the constraint has been violated. Since the INSERT statement fails, the entire body of work is rolled back. Therefore, no rows are inserted.

Oracle addressed this type of issue with the MERGE statement in their 9i release. If you are using Oracle 9i, then you might want to look at the MERGE statement.

If you are not using 9i, then a different way of performing the same thing is to use the MINUS or NOT IN operators to insert those rows from one table that are not found in the destination table. Taking your SQL statement one step further, it would look like this (let me assume COLUMN1 is the PK column):

INSERT INTO TAB1
   SELECT COLUMN1, COLUMN2, 'N', COLUMN3
   FROM  TAB2
   WHERE  TAB2.COLUMN1 NOT IN 
       (SELECT x.COLUMN1 FROM tab1 x);
This will insert those rows from TAB2 that do not yet exist in TAB1.

For More Information


This was first published in June 2002

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close