Change address location type for all contacts?

Question

I have a bunch of Organization contacts with "Work" addresses. I would like to change all of these to "Main" addresses. What is the most straightforward way to quickly get this done?

This is quite important, because when you have contacts with emails in different location types, you can't dedupe on import, see Import from CSV and dedupe using Email (match to contact) with PRIMARY email?

If I am wrong about this, please correct me!

Answer 1

I'd use SQL. Take a backup first, of course, and test on a non-production server. Then run:

UPDATE civicrm_address ca JOIN civicrm_contact cc ON ca.contact_id = cc.id 
SET location_type_id = 3 
WHERE location_type_id = 2 AND cc.contact_type = 'Organization';

Answer 2

I solved this using the Drupal modules CiviCRM Entity and Views Bulk Operations.

This gave me a very powerful way to search, filter, and mass-modify the addresses in our database.

1. Create a new view that shows CiviCRM Address entities and provides a page

2. Add a Bulk Operations: CiviCRM Address field and configure it under "Selected bulk operations" to "Modify entity values" and select "Address Location Type" under its "Operations settings"

3. Add a filter for CiviCRM Address: Address Location Type and set the location type that you want to change. Or expose the filter so you can filter at will. Refer to civicrm/admin/locationType?reset=1, hover the edit links to get the location ID from the URL
4. Visit your view's page and check the "Select all" box. "Modify entity values" and enter the ID of the location you want set on all addresses you have selected in the view

Answer 3

The problem is, that civicrm_contact and civicrm_address cannot be accessed at the same time due to triggers involved.

I solved that issue by searching the relevant contacts on the civicrm user interface and adding them to a new group (e.g. with group id = 111).

Then, the above statement could be:

update civicrm_address inner join civicrm_group_contact on
  civicrm_address.contact_id = civicrm_group_contact.contact_id 
  set civicrm_address.location_type_id= 3 where group_id = 111 and
  location_type_id=2;

Answer 4

Here's an alternative formulation of Jon's query that might work better for you: UPDATE civicrm_address ca SET location_type_id = 3 WHERE location_type_id = 2 AND ca.contact_id in (SELECT id from civicrm_contact where contact_type = 'Organization');