XPLR
asked on
Update Contact table without duplicate records
I have a contact table A and Contact table A. I want to merge or update all the table b to table A but do not want to create duplicates records. Say if there is alreays a Shane Smith i don ot want the same record to be entered twice. Both the tables have firstname, lastname, email.
ASKER
I want everything to be in ContactsA
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
insert into contactsA( firstname, lastname, email)
select firstname, lastname, email
from contactsB where not exists(select 1 from contactsA C
where C.firstname = contactsB.firstname
and C.lastname = contactsB.lastname
and C.email = contactsB.email)
maybe this?
insert into contactsA( firstname, lastname, email)
select firstname, lastname, email
from contactsB as b where not exists(select firstname from contactsA x
where (x.firstname = B.firstname
and x.lastname = B.lastname)
or x.email = B.email)
your table design is very poor you really need a contact identifier as the key...
take a step back and consider....
do i want to allow for multiple email id's per contact... (represents two organisations?)
do i want to allow multiple contacts to use the same email id..(family members/children?)
what do you expect to happen when someone changes thier name (e.g. marriage)
insert into contactsA( firstname, lastname, email)
select firstname, lastname, email
from contactsB as b where not exists(select firstname from contactsA x
where (x.firstname = B.firstname
and x.lastname = B.lastname)
or x.email = B.email)
your table design is very poor you really need a contact identifier as the key...
take a step back and consider....
do i want to allow for multiple email id's per contact... (represents two organisations?)
do i want to allow multiple contacts to use the same email id..(family members/children?)
what do you expect to happen when someone changes thier name (e.g. marriage)
I guess you have same table structure ,
so, just use UNION!
insert into table ContactsAll
select * from contactsA
union
select * from contactsB
I hope this help you!