Forum Stats

  • 3,816,299 Users
  • 2,259,166 Discussions
  • 7,893,446 Comments

Discussions

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:

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

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.
  • 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.
  • 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
  • 745400
    745400 Member Posts: 5
    Yes. Actually duplicates in one of the sets was issue.
This discussion has been closed.