Merging contacts in SQL table without creating duplicate entries

Question

I have a table that holds only two columns - a ListID and PersonID. When a person is merged with another in the system, I was to update all references from the "source" person to be references to the "destination" person.

Ideally, I would like to call something simple like

UPDATE MailingListSubscription
SET PersonID = @DestPerson
WHERE PersonID = @SourcePerson

However, if the destination person already exists in this table with the same ListID as the source person, a duplicate entry will be made. How can I perform this action without creating duplicated entries? (ListID, PersonID is the primary key)

EDIT: Multiple ListIDs are used. If SourcePerson is assigned to ListIDs 1, 2, and 3, and DestinationPerson is assigned to ListIDs 3 and 4, then the end result needs to have four rows - DestinationPerson assigned to ListID 1, 2, 3, and 4.

Answer 1

--out with the bad
DELETE
FROM MailingListSubscription
WHERE PersonId = @SourcePerson
  and ListID in (SELECT ListID FROM MailingListSubscription WHERE PersonID = @DestPerson)

--update the rest (good)
UPDATE MailingListSubscription
SET PersonId = @DestPerson
WHERE PersonId = @SourcePerson

Answer 2

I have to agree with David B here. Remove all the older stuff that shouldn't be there and then do your update.

Answer 3

Actually I think you should go back and reconsider your database design as you really shouldn't be in circumstances where you're changing the the primary key for a record as you're proposing to do - it implies that the PersonID column is not actually a suitable primary key in the first place.

My guess is your PersonID is exposed to your users, they've renumbered their database for some reason and you're syncing the change back in. This is generally a poor idea as it breaks audit trails and temporal consistency. In these circumstances it's generally better to use you're own non-changing primary key - usually an identity - and set up the PersonID that the users see as an attribute of that. It's extra work but will give you additional consistency and robustness in the long run.

A good rule of thumb is the primary key of a record should not be exposed to the users where possible and you should only do so after careful consideration. OK I confess to breaking this myself on numerous occasions but it's worth striving for where you can :-)

Answer 4

First you should subscribe destperson to all lists that SourcePerson is subscribed to that Destperson isn't already subscibed. Then delete all the SourcePersons subscriptions. This will work with multiple ListIDs.

Insert into MailingListSubscription
(
   ListID,
   PersonID
)
Select
   ListID,
   @DestPerson
From
   MailingListSubscription as t1
Where
   PersonID = @SourcePerson and
   Not Exists
   (
      Select *
      From MailingListSubscription as t2
      Where
         PersonID = @DestPerson and
         t1.ListID = t2.ListID
   )



Delete From MailingListSubscription
Where
   PersonID = @SourcePerson