canada flag  linkedinfacebook   Call Us Today: (613) 494-5010

CiviCRM - delete duplicate emails

civicrm logoAlthough CiviCRM community recommends not to manipulate the database directly, these queries are useful for deleting duplicate emails, phones and addresses within the contact

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:

ALT provides IT consulting and IT services. Contact us for more details.