6 Replies Latest reply: Jul 3, 2009 6:20 AM by 21205 RSS

    Merge statement is failing with primary key voilation

    710100
      I have a merge statement, which inserts or updates data into table sit_conflicts. And i have used all primary key columns in "ON clause" of a merge statement. Here is the merge statement...

      MERGE
      INTO sit_conflict sc
      USING (SELECT conflict_itemloc_link_id,
      item,
      location,
      itemloc_link_id,
      insert_date
      FROM TABLE(L_sit)) sct1
      ON (sc.item = sct1.item
      AND sc.location = sct1.location
      AND sc.conflict_itemloc_link_id = sct1.conflict_itemloc_link_id
      AND sc.itemloc_link_id = sct1.itemloc_link_id)
      WHEN matched THEN
      UPDATE SET sc.insert_date = sct1.insert_date
      WHEN NOT matched THEN
      INSERT (sc.conflict_itemloc_link_id,
      sc.item,
      sc.location,
      sc.itemloc_link_id,
      sc.insert_date)
      VALUES (sct1.conflict_itemloc_link_id,
      sct1.item,
      sct1.location,
      sct1.itemloc_link_id,
      sct1.insert_date);

      where L_sit is a PL/SQL table. If L_sit is null then i should get an error 'Can't insert null into <primary key columns>'. But user is failing with primary key violation?

      Does merge statement iterates for each reocord? If so why merge is trying to insert non-unique record?

      Regards
      Chaitanya
        • 1. Re: Merge statement is failing with primary key voilation
          B.Guillaumin
          You can definitly have 2 duplicate keys in your source dataset not existing in your target dataset

          Ex:
          TEST(ID PRIMARY KEY,VAL)
          1,X

          SOURCE (ID,VAL)
          1,Y
          2,W
          2,Z

          Unless you can be sure that your PL/SQL don't contains duplicates you can have ORA-00001
          • 2. Re: Merge statement is failing with primary key voilation
            21205
            Welcome to the forum!

            where L_sit is a PL/SQL table. If L_sit is null then i should get an error 'Can't insert null into <primary key columns>'. But user is failing with primary key violation?
            probably you mean Nested Table...

            Why should you get an error?
            Can't reproduce your problem.
            Show a copy-paste example including the exception you encounter
            SQL> create table t (id number, name varchar2(10));
            
            Table created.
            
            SQL> 
            SQL> create type numlist is table of number
              2  /
            
            Type created.
            
            SQL> 
            SQL> 
            SQL> merge into t
              2  using (select column_value id
              3              , 'thing' name
              4           from table (numlist (1,2,3,4,5))
              5          where 1=0
              6        ) sctl
              7  on (sctl.id = t.id)
              8  when matched then update set name = upper (name)
              9  when not matched then insert values (sctl.id, sctl.name)
             10  /
            
            0 rows merged.
            
            SQL> 
            SQL> 
            SQL> select *
              2    from t
              3  /
            
            no rows selected
            
            SQL> 
            • 3. Re: Merge statement is failing with primary key voilation
              710100
              Hi Alex,

              My primary question:

              SQL*Plus: Release 9.0.1.4.0 - Production on Fri Jul 3 05:05:00 2009

              (c) Copyright 2001 Oracle Corporation. All rights reserved.


              Connected to:
              Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
              With the Partitioning, Data Mining and Real Application Testing options

              SQL> create type numlist is table of number;
              2 /

              Type created.

              SQL> create table t (id number primary key, var varchar2(20));

              Table created.

              SQL> select column_value id,
              2 'Abc' var
              3 from table(numlist(1,1,2,3,4));

              ID VAR
              ---------- ---
              1 Abc
              1 Abc
              2 Abc
              3 Abc
              4 Abc

              SQL> merge into t
              2 using (select column_value id,
              3 'Abc' var
              4 from table(numlist(1,1,2,3,4)))sct1
              5 on (sct1.id = t.id)
              6 when matched then update set var = 'Abc'
              7 when not matched then insert values (sct1.id, sct1.var);
              merge into t
              *
              ERROR at line 1:
              ORA-00001: unique constraint (RACHAPC_RMSD120A.SYS_C00531954) violated

              Why am i getting unique constraint error? It's failing while inserting second record, why merge could not match the first record inserted?

              And my secondary question: If a value in pl/sql table is NULL i'll get a different error, not a unique constraint error. I am clear with this. I am not clear with the above case.

              SQL> ed
              Wrote file afiedt.buf

              1 merge into t
              2 using (select column_value id,
              3 'Abc' var
              4 from table(numlist(NULL,1,2,3,4)))sct1
              5 on (sct1.id = t.id)
              6 when matched then update set var = 'Abc'
              7* when not matched then insert values (sct1.id, sct1.var)
              SQL> /
              when not matched then insert values (sct1.id, sct1.var)
              *
              ERROR at line 7:
              ORA-01400: cannot insert NULL into ("RACHAPC_RMSD120A"."T"."ID")
              • 4. Re: Merge statement is failing with primary key voilation
                21205
                1) In your Nested Table there are duplicates (you got two "1") in there.
                Your Merge would be similar to
                insert all
                into t values (id, name)
                select 1 id, 'test' name from dual 
                union all
                select 1, 'test' from dual
                It's a single statement, which is validated at the end of the statement.

                2) Same as 1)
                a Merge is a single statement, the data should be valid at the end of the statement
                • 5. Re: Merge statement is failing with primary key voilation
                  710100
                  Duplicates should fall under match condition.. why it's going to insert block (not matched). Merge is update if matched or insert if unmatched. In may case first record (1) is inserted.. the second record should go to Update right?

                  Regards
                  Chaitanya
                  • 6. Re: Merge statement is failing with primary key voilation
                    21205
                    user773320 wrote:
                    Duplicates should fall under match condition.. why it's going to insert block (not matched). Merge is update if matched or insert if unmatched. In may case first record (1) is inserted.. the second record should go to Update right?
                    No, the duplicates are in the source.. The MATCHED/NOT MATCHED is done on the target. Look at it this way: it is like inserting two similar records at the same time. Take another look at the INSERT ALL example I gave before.