Problem exporting due to multiple addresses of one location type (e.g. two 'home' addresses)

Question

I have run into a problem where someone created a Webform using Webform_CiviCRM and for some reason added two address field sets to the contact, with the street address in one of them and the city/state/zip in the other. Now the contacts in CiviCRM have two incomplete addresses, both of the type 'Home'. When I try to export them, I'm just getting one of the incomplete addresses.

This old forum thread seems to be related. It looks like the UI on the main contact page prevents creation of multiple addresses of the same type, but the API must still not enforce?

Anyone have a suggestion for how to tackle this and get the addresses merged/squashed into a single address?

Answer 1

I would fix this either partially or wholly with SQL. The general approach I'd use is:

• Change the location_type_id on one of the two addresses for each contact to an unused location type (creating a new location type if need be).
• Export the contacts with both the "Home" and the unused location type.
• Reimport the data with all address fields designated as "Home" addresses.
• Delete the extra addresses with a SQL statement.

The basic query you want is:

SELECT MAX(id), contact_id, COUNT(id) FROM civicrm_address WHERE location_type_id = 1 GROUP BY contact_id HAVING COUNT(contact_id) > 1;

MAX(id) is the id of the second address created; MIN(id) would return the first address id.

Due to this constraint of MySQL, you can't just use this SQL query that would meet the requirements of the first bullet point:

UPDATE civicrm_address SET location_type_id=4 WHERE id IN (SELECT MAX(id), contact_id, COUNT(id) FROM civicrm_address WHERE location_type_id = 1 GROUP BY contact_id HAVING COUNT(contact_id) > 1);

You can use of the answers on that SE question to work around that (e.g. with a temp table).