Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Can I write merge SQL statement having count(*)?

951304Jul 28 2012 — edited Aug 2 2012
Hi this is a followup question of my previous post. I tried to use a merge SQL statement to solve my problem but now I bump into another problem.
Now I have a table where the field I need to update is a partial PK
when using a merge SQL statement, I have to put a where clause that check if count(*) = 1 because of the PK constraint
Where can I put the count(*) = 1 clause?

Here are the details:
I have two tables TA and TB, where TA contains the fields ID, FULLNAME, TYPE and TB contains the fields ID, FIRSTNAME
I want to update the firstnames in TB to be the firstnames from TA where TB.ID = TA.ID and TA.TYPE = 'ABC'
{ID, FIRSTNAME} are PKs but for the same ID, there can be more than 1 firstname.
e.g.
TA
-------------------------------
ID | FULLNAME | TYPE
1 Caroline T ABC
2 Mary C DEF
3 Peter J ABC

TB
----------------------
ID | FIRSTNAME
1 Caroline
1 Carol
1 C,
3 Peter

I need to update TB with the new firstnames from TA where type is 'ABC' but only for those fields that have count(TB.ID) = 1
when I try to run this SQL statement

merge into TB B using TA A
on (A.ID = B.ID and A.TYPE = 'ABC')
when matched then update set B.FIRSTNAME = substr(A.FULLNAME, 1, instr(A.FULLNAME, ',') - 1)

I got this error SQL Error: ORA-00001: unique constraint (TEST.PK_TB) violated
which I believe is because I updated those fields say ID = 1, all with 'Caroline'
that means I will have to add a clause having count(TB.ID) = 1
How would you do it?

Server is Oracle 11g
Thank you!
This post has been answered by Frank Kulash on Jul 28 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 30 2012
Added on Jul 28 2012
3 comments
500 views