I've noticed that some of our contact records have the same email duplicated (e.g. as 'home' and 'work' or 'main' and 'work' emails). I'm not seeing a simple way to find these records so we can clean them up - is there any way to 'dedupe' or check for this kind of duplication?
(Similarly, we have some records where the address is duplicated. In both cases, I think it's either from merging records or importing contacts with the fields mismatched)
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:
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 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
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 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
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 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
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 <> ''
I don't know a way within Civi so my suggestion would be:
Same could be done for addresses - it's the contact_address database table in that case.
