Answers.Body: <p>I would fix this either partially or wholly with SQL.  The general approach I'd use is:</p>
  
  <ul>
  <li>Change the <code>location_type_id</code> on one of the two addresses for each contact to an unused location type (creating a new location type if need be).</li>
  <li>Export the contacts with both the "Home" and the unused location type.</li>
  <li>Reimport the data with all address fields designated as "Home" addresses.</li>
  <li>Delete the extra addresses with a SQL statement.</li>
  </ul>
  
  <p>The basic query you want is:</p>
  
  <pre><code>SELECT MAX(id), contact_id, COUNT(id) FROM civicrm_address WHERE location_type_id = 1 GROUP BY contact_id HAVING COUNT(contact_id) &gt; 1;
  </code></pre>
  
  <p><code>MAX(id)</code> is the id of the second address created; <code>MIN(id)</code> would return the first address id.</p>
  
  <p><a href="https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause">Due to this constraint of MySQL</a>, you can't just use this SQL query that would meet the requirements of the first bullet point:</p>
  
  <pre><code>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) &gt; 1);
  </code></pre>
  
  <p>You can use of the answers on that SE question to work around that (e.g. with a temp table).</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-05T17:38:42.723Z
Answers.LastActivityDate: 2017-01-05T17:38:42.723Z
Answers.LastEditDate: 2017-05-23T12:38:59.763Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Body: <p>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.</p>
  
  <p><a href="https://forum.civicrm.org/index.php?topic=34024.0" rel="nofollow noreferrer">This old forum thread seems to be related</a>. 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?</p>
  
  <p><a href="https://i.sstatic.net/Nk7Ko.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/Nk7Ko.png" alt="UI Error message RE: addresses"></a></p>
  
  <p>Anyone have a suggestion for how to tackle this and get the addresses merged/squashed into a single address?</p>
  
Tags: <webform-civicrm><export><addresses><duplicate-contacts>
Title: Problem exporting due to multiple addresses of one location type (e.g. two 'home' addresses)
ThreadText: # Problem exporting due to multiple addresses of one location type (e.g. two 'home' addresses)
  
  (Asked by: username_0 on 2017-01-04)
  
  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](https://forum.civicrm.org/index.php?topic=34024.0). 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?
  
  [![UI Error message RE: addresses](https://i.sstatic.net/Nk7Ko.png)](https://i.sstatic.net/Nk7Ko.png)
  
  Anyone have a suggestion for how to tackle this and get the addresses merged/squashed into a single address?
  
  **Tags:** webform-civicrm, export, addresses, duplicate-contacts
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2017-01-05. Score: 2 | Accepted answer
  
  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](https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause), 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).
  
  
  ---
  
  