Can I write merge SQL statement having count(*)?
951304Jul 28 2012 — edited Aug 2 2012Hi 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!