Solved

merge table return error: ORA-00001: unique constraint

Posted on 2006-06-24
6
4,646 Views
Last Modified: 2008-01-09
Hello,

I am trying to resolve ORA-00001: unique constraint (OCP.PK_MOVIES) violated error and don't understand cause of this problem.

I have a table name =  movies:

sql> create table movies
(movie_name varchar2(30),
showtime varchar2(30))

alter table movies
add constraint pk_movies primary key(movie_name);

insert into movies values('GONE WITH THE WIND','8:00 PM')
/

When I issue merge :

merge into movies M1
using movies m2 on (m2.movie_name = m1.movie_name
                    and m2.movie_name = 'ABC NEWS')
when matched then update set m1.showtime = '9:00 PM'
when not matched then insert (m1.movie_name, m1.showtime)
values ('ABC NEWS','9:00 PM')

table insert row succesfully.

when I execute the same merge command:

I get  ORA-00001: unique constraint  which makes sense because of the primary key. However, when i change the movie name in the 3rd line I still
get ORA-00001: unique constraint .

Can someone please tell me why Oracle is returning ora-0001 unique constraint error when I execute below code using merge command

merge into movies M1
using movies m2 on (m2.movie_name = m1.movie_name
                    and m2.movie_name = 'XYZ NEWS')
when matched then update set m1.showtime = '9:00 PM'
when not matched then insert (m1.movie_name, m1.showtime)
values ('XYZ NEWS','9:00 PM')
/


Thanks in advance.  

-Mohammadzahid



0
Comment
Question by:mohammadzahid
  • 3
  • 3
6 Comments
 
LVL 9

Accepted Solution

by:
neo9414 earned 500 total points
ID: 16977590
there's a problem in which you are trying to do the merge....
when you execute the below query

merge into movies M1
using movies m2 on (m2.movie_name = m1.movie_name
                    and m2.movie_name = 'XYZ NEWS')
when matched then update set m1.showtime = '9:00 PM'
when not matched then insert (m1.movie_name, m1.showtime)
values ('XYZ NEWS','9:00 PM');

see what happens internally...

the above "on" condition causes both the rows already present to represent mismatch. so it tries to insert same row twice and hence causes failure.

remove the primary key and execute the above statement again and see what happens.

alter table movies drop primary key;

SQL>merge into movies M1
        using movies m2 on (m2.movie_name = m1.movie_name
                    and m2.movie_name = 'XYZ NEWS')
       when matched then update set m1.showtime = '9:00 PM'
       when not matched then insert (m1.movie_name, m1.showtime)
       values ('XYZ NEWS','9:00 PM');

2 rows merged.

SQL>select * from movies;

MOVIE_NAME                     SHOWTIME
------------------------------ -----------
GONE WITH THE WIND             8:00 PM
ABC NEWS                       9:00 PM
XYZ NEWS                       9:00 PM
XYZ NEWS                       9:00 PM


cheers,
Ajay


0
 
LVL 11

Author Comment

by:mohammadzahid
ID: 16977777
Thanks for the quick response. If I disable the primary key then 2 rows are inserted in movies table. But, I still don't understand why same row is getting inserted twice when XYZ NEWS don't exist in movies table.
0
 
LVL 9

Expert Comment

by:neo9414
ID: 16978046
none of the row matches with the condition of the merge statement. since you have two rows already in the table, it will insert two rows. just insert 3 rows in the table using plain insert statements. now if you tried your merge statement it will add 3 rows. This is how it will work and is supposed to work.

to solve your problem, you will have to do it the old way using a pl/sql procedure. do you want me to write one for you. its very simple.

cheers...

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Author Comment

by:mohammadzahid
ID: 16980386
Sorry, I don't understand merge behavior. If XYZ NEWS data does not exist in the movies table then why ora-0001 is occuring?
Even if I manually insert XYZ NEWS in my table and hope merge will update time of the movie. ora-0001 error is still returning.

Why is Oracle trying to insert duplicate rows of data in the table? Does insert statement gets executed twice in my code?

Thanks.
0
 
LVL 9

Expert Comment

by:neo9414
ID: 16980591
the merge statement is comparing all the rows in movies table to find matching and not matching rows. your condition is such that all the rows do not match and for each row not matched, the condition "When not matched" is executed which inserts the row. so if there is 2 row already inserted, it will insert 2 row. if it has 3 rows, it will insert 3 rows and so on...

0
 
LVL 11

Author Comment

by:mohammadzahid
ID: 16980677
This makes sense. Thanks for the explanation and your quick responses are appreciated.

Thanks.



 
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle SQL Select unique values from two columns 4 53
Oracle SQL - Query help 7 54
constraint check 2 40
Not able to drop or recreate an Oracle stored procedure 1 30
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question