Forum Stats

  • 3,816,315 Users
  • 2,259,170 Discussions
  • 7,893,452 Comments

Discussions

Categories

Merge stmt trying to insert duplicate while the ON clause is based on index

745400
745400 Member Posts: 5
edited Jan 15, 2010 10:38AM in General Database Discussions
Hi,

I am using a merge statement to insert record in a table. The table has an unique index based on four columns. The merge statement matches on all four column (a.column1 = b.column1 and a.column2 = b.column2 ... etc.) in the ON clause. If a match is found then it does nothing if not found corresponding record is inserted.

But for some reason it is trying to insert duplicate and I am getting unique constraint violated exception.

The database is huge and the source table contains more than 200,000 records.

Any idea how it can happen?
Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Dom Brooks
    Dom Brooks Member Posts: 5,558 Silver Crown
    edited Jan 15, 2010 4:40AM Answer ✓
    How can it happen?

    Easily if there is a duplicate in the data you want to merge in, bearing in mind that this is a set-based operation with the two sets of data evaluated using the standard read consistency model at the start of the query. 
    Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> DROP TABLE t1;

Table dropped.

SQL>
SQL> CREATE TABLE t1
  2  (col1 NUMBER PRIMARY KEY);

Table created.

SQL>
SQL> MERGE
  2  INTO  t1
  3  USING (SELECT 1 x
  4         FROM   DUAL
  5         UNION ALL
  6         SELECT 1
  7         FROM   DUAL) x
  8  ON    (x.x = t1.col1)
  9  WHEN NOT MATCHED THEN
 10         INSERT
 11         VALUES
 12         (      x.x);
MERGE
*
ERROR at line 1:
ORA-00001: unique constraint (ROS.SYS_C0088944) violated


SQL> rollback;

Rollback complete.

SQL> MERGE
  2  INTO  t1
  3  USING (SELECT DISTINCT x
  4         FROM (
  5            SELECT  1 x
  6            FROM   DUAL
  7            UNION ALL
  8            SELECT 1
  9            FROM   DUAL)) x
 10  ON    (x.x = t1.col1)
 11  WHEN NOT MATCHED THEN
 12         INSERT
 13         VALUES
 14         (      x.x);

1 row merged.

SQL>
    Edited by: DomBrooks on Jan 15, 2010 9:36 AM
    · Share on TwitterShare on Facebook

Answers

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    The database is huge and the source table contains more than 200,000 records.
    Any bug will not depend upon the number of rows.

    Post Operating System (OS) name & version for DB server system.
    Post results of
    SELECT * from v$version

    It would be helpful if you provided DDL for tables involved.
    It would be helpful if you provided DML for test data.
    It would be helpful if you provided the SQL that produces reported results so we reproduce what you claim.
    · Share on TwitterShare on Facebook
  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    Maybe you have more than one unique constraint (or index) on this table?

    Aside from guessing, you'd have to provide a lot more information as previously mentioned.
    · Share on TwitterShare on Facebook
  • Dom Brooks
    Dom Brooks Member Posts: 5,558 Silver Crown
    edited Jan 15, 2010 4:40AM Answer ✓
    How can it happen?

    Easily if there is a duplicate in the data you want to merge in, bearing in mind that this is a set-based operation with the two sets of data evaluated using the standard read consistency model at the start of the query. 
    Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> DROP TABLE t1;

Table dropped.

SQL>
SQL> CREATE TABLE t1
  2  (col1 NUMBER PRIMARY KEY);

Table created.

SQL>
SQL> MERGE
  2  INTO  t1
  3  USING (SELECT 1 x
  4         FROM   DUAL
  5         UNION ALL
  6         SELECT 1
  7         FROM   DUAL) x
  8  ON    (x.x = t1.col1)
  9  WHEN NOT MATCHED THEN
 10         INSERT
 11         VALUES
 12         (      x.x);
MERGE
*
ERROR at line 1:
ORA-00001: unique constraint (ROS.SYS_C0088944) violated


SQL> rollback;

Rollback complete.

SQL> MERGE
  2  INTO  t1
  3  USING (SELECT DISTINCT x
  4         FROM (
  5            SELECT  1 x
  6            FROM   DUAL
  7            UNION ALL
  8            SELECT 1
  9            FROM   DUAL)) x
 10  ON    (x.x = t1.col1)
 11  WHEN NOT MATCHED THEN
 12         INSERT
 13         VALUES
 14         (      x.x);

1 row merged.

SQL>
    Edited by: DomBrooks on Jan 15, 2010 9:36 AM
    · Share on TwitterShare on Facebook
  • 745400
    745400 Member Posts: 5
    Yes. Actually duplicates in one of the sets was issue.
    · Share on TwitterShare on Facebook
This discussion has been closed.