Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

merge table return error: ORA-00001: unique constraint

Posted on 2006-06-24
6
Medium Priority
?
4,984 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 2000 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

618 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