Ask the Expert

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:
      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.

    Requires Free Membership to View

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):

       (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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: