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!

  • If Joe's answer worked, please accept it by clicking on the tick beside it. SE works best if the correct answer is clearly identified. – JoAnne Jun 13 '15 at 8:13
  • I definitely will as soon as I get a chance to try it; I've been out sick for several days. Thanks for the reminder. – Link Swanson Jun 15 '15 at 15:28

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';
  • Thank you! I tried your query but it errors with ERROR 1442 (HY000): Can't update table 'civicrm_contact' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. – Link Swanson Jun 9 '15 at 19:31
  • Can anyone reproduce these SQL errors? Any ideas on where I should look to figure out why the queries suggested by Jon G and @Joe Murray fail with this error? – Link Swanson Jun 29 '15 at 21:09
  • @LinkSwanson Yeah - I wrote this SQL in my head, and didn't account for triggers. You can find comments on other StackExchange sites on working around this...but if I wanted to fix this really quick and dirty, I'd put the site into maintenance mode, delete all the triggers in MySQL, run the SQL above, then go into Civi and rebuild the triggers: q=civicrm/menu/rebuild&reset=1&triggerRebuild=1 – Jon G - Megaphone Tech Jun 29 '15 at 21:25
  • 1
    Using a temporary table should do the trick and it's easier than delete / rebuild trigger. – samuelsov Dec 18 '15 at 15:32
up vote 5 down vote accepted

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
  • 1
    Great. Another good use for Entities and VBO. Can you 'tick' your answer to show it was accepted by you? – petednz - fuzion Aug 27 '15 at 19:57
  • Also, are you okay if I add this as another use case for civi and VBO to our blog here, fuzion.co.nz/tags/vbo as I would like to expand on how these Fuzion modules can be helpful to users? – petednz - fuzion Aug 27 '15 at 19:58
  • 1
    Yes, go ahead and use it. It seems to be a useful way of managing and cleaning up data. – Link Swanson Aug 27 '15 at 20:15
  • Cool, thanks. done – petednz - fuzion Aug 27 '15 at 21:52
  • Hey @petednz-fuzion, I noticed that the VBO is not able to modify custom address fields, even though they appear in the modify entity values selection. It goes through the batch but the values are not modified. – Link Swanson Sep 1 '15 at 17:06

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');

  • Thanks for your query; however, it gives the same ERROR 1442 (HY000): Can't update table 'civicrm_contact' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. Not sure what the already used by statement business is caused by. – Link Swanson Jun 16 '15 at 16:41
  • I am getting the same problem. Why would this update civicrm_contact? – Josh Oct 21 at 18:39

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;

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.