Deduping within contact records

Question

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)

Answer 1

I was asked by a client to clean up this exact situation this week, which I did via SQL statements (below).

These SQL statements are NOT perfect, because they fail to account for whether an email/etc. is primary when deciding which to keep (it keeps the first one that was created). I have a second set of SQL statements (also below) to ensure that each record has a "primary" email/phone/address. However, if the record has multiple non-duplicate emails/phones/addresses, you could end up switching the primary. That's certainly solveable - I encourage edits! - but out of scope for me personally.

Delete duplicate emails:

DELETE civicrm_email.* FROM civicrm_email LEFT JOIN( SELECT MIN(id) min_id FROM civicrm_email e JOIN (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.min_id WHERE ids.min_id IS NULL AND civicrm_email.contact_id IS NOT NULL

Delete duplicate phones:

DELETE civicrm_phone.* FROM civicrm_phone LEFT JOIN( SELECT MIN(id) min_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.min_id WHERE ids.min_id IS NULL AND civicrm_phone.contact_id IS NOT NULL

Delete duplicate addresses:

DELETE civicrm_address.* FROM civicrm_address LEFT JOIN( SELECT MIN(id) min_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.min_id WHERE ids.min_id IS NULL AND civicrm_address.contact_id IS NOT NULL AND civicrm_address.street_address <> ''

Set a primary email:

UPDATE civicrm_email e JOIN (SELECT MIN(id) min_id from civicrm_email GROUP BY contact_id HAVING SUM(is_primary) = 0 AND contact_id IS NOT NULL) f ON e.id = f.min_id SET is_primary = 1

Set a primary phone:

UPDATE civicrm_phone e JOIN (SELECT MIN(id) min_id from civicrm_phone GROUP BY contact_id HAVING SUM(is_primary) = 0 AND contact_id IS NOT NULL) f ON e.id = f.min_id SET is_primary = 1

Set a primary address:

UPDATE civicrm_address e JOIN (SELECT MIN(id) min_id from civicrm_address GROUP BY contact_id HAVING SUM(is_primary) = 0 AND contact_id IS NOT NULL) f ON e.id = f.min_id SET is_primary = 1

Answer 2

I don't know a way within Civi so my suggestion would be:

1. Export your contacts' details to CSV or if happy delving into the database export the contact_email table to CSV
2. Do some cell match formatting in Excel to highlight those with duplicate emails
3. Edit each highlighted contact.

Same could be done for addresses - it's the contact_address database table in that case.