[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5049
  • Last Modified:

merge table return error: ORA-00001: unique constraint

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
mohammadzahid
Asked:
mohammadzahid
  • 3
  • 3
1 Solution
 
neo9414Commented:
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
 
mohammadzahidAuthor Commented:
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
 
neo9414Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mohammadzahidAuthor Commented:
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
 
neo9414Commented:
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
 
mohammadzahidAuthor Commented:
This makes sense. Thanks for the explanation and your quick responses are appreciated.

Thanks.



 
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now