7

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!

2
  • 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, 2015 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.
    – LunkRat
    Jun 15, 2015 at 15:28

4 Answers 4

8

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';
4
  • 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.
    – LunkRat
    Jun 9, 2015 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?
    – LunkRat
    Jun 29, 2015 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 Jun 29, 2015 at 21:25
  • 1
    Using a temporary table should do the trick and it's easier than delete / rebuild trigger.
    – samuelsov
    Dec 18, 2015 at 15:32
6

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
4
  • 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, 2015 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, 2015 at 19:58
  • 1
    Yes, go ahead and use it. It seems to be a useful way of managing and cleaning up data.
    – LunkRat
    Aug 27, 2015 at 20:15
  • 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.
    – LunkRat
    Sep 1, 2015 at 17:06
2

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;
1
  • I would recommend also adding and status = 'Added' to your condition (to avoid matching contacts that were added then removed from a group).
    – bgm
    Dec 21, 2018 at 16:43
0

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

2
  • 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.
    – LunkRat
    Jun 16, 2015 at 16:41
  • I am getting the same problem. Why would this update civicrm_contact?
    – Josh
    Oct 21, 2018 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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