2019 UPDATE: A year later, I had to revisit this problem space, and I improved on my old answer; ignore earlier discussion threads on this answer.
When deleting duplicated emails/phone/addresses within a contact, you're inherently losing data. E.g. if a phone number is both "Main" and "Work", and you delete one, you have to choose which location type you're losing.
The following SQL statements handle three strategies I identified:
- Keep the record marked as "primary". Delete the rest.
- Keep the oldest record. Delete the rest.
- Keep the newest record. Delete the rest.
My preferred strategy is to keep the primary, delete the rest. This does NOT catch duplicates where none of the dupes are primary, so I follow that with a "keep the newest record" statement to catch the stragglers.
Protip: To see what records will be deleted before deleting, change DELETE
to SELECT
.
Delete duplicate emails, keeping the primary:
-- Delete all emails from Civi that are in the "duplicated" subselect but aren't primary.
DELETE e.*
FROM civicrm_email e JOIN (
-- Select all duplicated emails
SELECT contact_id, email
FROM civicrm_email
GROUP BY contact_id, email
HAVING COUNT(*) > 1
) AS f ON f.email = e.email
AND f.contact_id = e.contact_id
WHERE is_primary = 0
Delete duplicate emails, keeping the oldest:
Change MIN
to MAX
to keep the newest.
-- Compare civicrm_email to the outer subselect. If your email/contact_id matches but you don't have the minimum email ID, delete.
DELETE civicrm_email.*
FROM civicrm_email
LEFT JOIN(
-- Select the lowest email id for each value in the inner subselect
SELECT MIN(id) keep_id
FROM civicrm_email e
JOIN (
-- Get a list of unique email/contact_id combos
SELECT contact_id, email
FROM civicrm_email
GROUP BY contact_id, email
) AS f ON f.email = e.email
AND f.contact_id = e.contact_id
GROUP BY e.email, e.contact_id) ids ON civicrm_email.id = ids.keep_id
WHERE ids.keep_id IS NULL AND civicrm_email.contact_id IS NOT NULL
Delete duplicate phones, keeping the primary:
-- Delete all numbers from Civi that are in the "duplicated" subselect but aren't primary.
DELETE e.*
FROM civicrm_phone e JOIN (
-- Select all duplicated phone numbers
SELECT contact_id, phone
FROM civicrm_phone
GROUP BY contact_id, phone
HAVING COUNT(*) > 1
) AS f ON f.phone = e.phone
AND f.contact_id = e.contact_id
WHERE is_primary = 0
Delete duplicate phones, keeping the oldest:
Change MIN
to MAX
to keep the newest.
SELECT civicrm_phone.*
FROM civicrm_phone
LEFT JOIN(
SELECT MIN(id) keep_id
FROM civicrm_phone e JOIN (
SELECT contact_id, phone
FROM civicrm_phone
GROUP BY contact_id, phone
) AS f ON f.phone = e.phone
AND f.contact_id = e.contact_id
GROUP BY e.phone, e.contact_id
) ids ON civicrm_phone.id = ids.keep_id
WHERE ids.keep_id IS NULL AND civicrm_phone.contact_id IS NOT NULL
Delete duplicate addresses, keeping the primary:
-- Delete all addresses from Civi that are in the "duplicated" subselect but aren't primary.
DELETE e.*
FROM civicrm_address e JOIN (
-- Select all duplicated addresses
SELECT contact_id, street_address
FROM civicrm_address
GROUP BY contact_id, street_address
HAVING COUNT(*) > 1
) AS f ON f.street_address = e.street_address
AND f.contact_id = e.contact_id
WHERE is_primary = 0
Delete duplicate addresses, keeping the oldest:
Change MIN
to MAX
to keep the newest.
DELETE civicrm_address.*
FROM civicrm_address
LEFT JOIN(
SELECT MIN(id) keep_id
FROM civicrm_address e
JOIN (
SELECT contact_id, street_address
FROM civicrm_address
GROUP BY contact_id, street_address
) AS f ON f.street_address = e.street_address
AND f.contact_id = e.contact_id
GROUP BY e.street_address, e.contact_id
) ids ON civicrm_address.id = ids.keep_id
WHERE ids.keep_id IS NULL
AND civicrm_address.contact_id IS NOT NULL
AND civicrm_address.street_address <> ''