Solved

merge table return error: ORA-00001: unique constraint

Posted on 2006-06-24
6
4,805 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

717 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