Solved

merge table return error: ORA-00001: unique constraint

Posted on 2006-06-24
6
4,533 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now