Answers.Body: <p>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.</p>
  <p>When deleting duplicated emails/phone/addresses within a contact, you're inherently losing data.  E.g. if a phone number is both &quot;Main&quot; and &quot;Work&quot;, and you delete one, you have to choose which location type you're losing.</p>
  <p>The following SQL statements handle three strategies I identified:</p>
  <ul>
  <li>Keep the record marked as &quot;primary&quot;.  Delete the rest.</li>
  <li>Keep the oldest record.  Delete the rest.</li>
  <li>Keep the newest record.  Delete the rest.</li>
  </ul>
  <p>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 &quot;keep the newest record&quot; statement to catch the stragglers.</p>
  <p><strong>Protip:</strong> To see what records will be deleted before deleting, change <code>DELETE</code> to <code>SELECT</code>.</p>
  <h3>Delete duplicate emails, keeping the primary:</h3>
  <pre><code>-- Delete all emails from Civi that are in the &quot;duplicated&quot; 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(*) &gt; 1
  ) AS f ON f.email = e.email
  AND f.contact_id = e.contact_id
  WHERE is_primary = 0
  </code></pre>
  <h3>Delete duplicate emails, keeping the oldest/newest:</h3>
  <p>Change <code>MIN</code> to <code>MAX</code> to keep the newest.</p>
  <pre><code>-- 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
  </code></pre>
  <h3>Delete duplicate websites, keeping the oldest/newest:</h3>
  <p>Change <code>MIN</code> to <code>MAX</code> to keep the newest.</p>
  <pre><code>-- 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
  </code></pre>
  <h3>Delete duplicate phones, keeping the primary:</h3>
  <pre><code>-- Delete all numbers from Civi that are in the &quot;duplicated&quot; 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(*) &gt; 1
  ) AS f ON f.phone = e.phone
  AND f.contact_id = e.contact_id
  WHERE is_primary = 0
  </code></pre>
  <h3>Delete duplicate phones, keeping the oldest:</h3>
  <p>Change <code>MIN</code> to <code>MAX</code> to keep the newest.</p>
  <pre><code>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
  </code></pre>
  <h3>Delete duplicate addresses, keeping the primary:</h3>
  <pre><code>-- Delete all addresses from Civi that are in the &quot;duplicated&quot; 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(*) &gt; 1
  ) AS f ON f.street_address = e.street_address
  AND f.contact_id = e.contact_id
  WHERE is_primary = 0
  </code></pre>
  <h3>Delete duplicate addresses, keeping the oldest:</h3>
  <p>Change <code>MIN</code> to <code>MAX</code> to keep the newest.</p>
  <pre><code>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 &lt;&gt; ''
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-06-12T09:30:24.137Z
Answers.Text: 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.
Answers.UserDisplayName: liblogger
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-06-21T16:31:58.643Z
Answers.Text: @liblogger You probably don't need this anymore, but I revisited this and updated the answer to reflect a strategy to keep primary contact info.
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-06-07T14:42:01.793Z
Answers.LastActivityDate: 2021-08-11T16:06:14.000Z
Answers.LastEditDate: 2021-08-11T16:06:14.000Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.Body: <p>I don't know a way within Civi so my suggestion would be:</p>
  
  <ol>
  <li>Export your contacts' details to CSV or if happy delving into the database export the contact_email table to CSV</li>
  <li>Do some cell match formatting in Excel to highlight those with duplicate emails</li>
  <li>Edit each highlighted contact.</li>
  </ol>
  
  <p>Same could be done for addresses - it's the contact_address database table in that case.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-06-12T09:32:17.950Z
Answers.Text: 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.
Answers.UserDisplayName: liblogger
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-06-07T14:21:31.233Z
Answers.LastActivityDate: 2018-06-07T14:21:31.233Z
Answers.UserDisplayName: oskylark
Body: <p>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?</p>
  
  <p>(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)</p>
  
Tags: <contacts><duplicate-contacts>
Title: Deduping within contact records
ThreadText: # Deduping within contact records
  
  (Asked by: username_0 on 2018-06-06)
  
  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)
  
  **Tags:** contacts, duplicate-contacts
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2018-06-07. Score: 6
  
  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 <> ''
  
  ```
  
  > **Comments:**
  > **username_0**: 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.  
  > **username_1**: @username_0 You probably don't need this anymore, but I revisited this and updated the answer to reflect a strategy to keep primary contact info.  
  
  ---
  
  ### Answer by username_2 on 2018-06-07. Score: 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.
  
  > **Comments:**
  > **username_0**: 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.  
  
  ---
  
  