7

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)

5

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

  • Thanks - I will give this a try when I have a chance to set up a copy of the site. Losing the primary email could be a problem, although I'm not sure how many contacts we have with multiple emails that aren't duplicated. Might need to do the spreadsheet checking below just to get a sense of the scale of it. – liblogger Jun 12 '18 at 9:30
1

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.

  • Thanks - it does look like this is the only quick option for checking for duplicates. Hopefully the problem isn't as widespread as my first impression and it's not too arduous to go through the contact records individually. – liblogger Jun 12 '18 at 9:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.