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

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/newest:

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 websites, keeping the oldest/newest:

Change MIN to MAX to keep the newest.

-- Compare civicrm_website to the outer subselect.  If your website/contact_id matches but you don't have the minimum website ID, delete.
DELETE civicrm_website.*
FROM civicrm_website
LEFT JOIN(
  -- Select the lowest website id for each value in the inner subselect
  SELECT MIN(id) keep_id
  FROM civicrm_website w
  JOIN (
    -- Get a list of unique website/contact_id combos
    SELECT contact_id, url
    FROM civicrm_website
    GROUP BY contact_id, url
  ) AS f ON f.url = w.url
  AND f.contact_id = w.contact_id
  GROUP BY w.url, w.contact_id) ids ON civicrm_website.id = ids.keep_id
  WHERE ids.keep_id IS NULL AND civicrm_website.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 <> ''

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.