[SOLVED] Merging contacts in SQL table without creating duplicate entries

Merging contacts in SQL table without creating duplicate entries

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.

Solution

--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