Answers.Body: <p>I'd use SQL. Take a backup first, of course, and test on a non-production server.  Then run:</p>
  
  <pre><code>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';
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-06-09T19:31:30.500Z
Answers.Text: 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.`
Answers.UserDisplayName: LunkRat
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-06-29T21:09:21.300Z
Answers.Text: 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?
Answers.UserDisplayName: LunkRat
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-06-29T21:25:19.967Z
Answers.Text: @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
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-12-18T15:32:57.560Z
Answers.Text: Using a temporary table should do the trick and it's easier than delete / rebuild trigger.
Answers.UserDisplayName: samuelsov
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-06-08T22:13:34.647Z
Answers.LastActivityDate: 2015-06-08T22:13:34.647Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.Body: <p>The problem is, that civicrm_contact and civicrm_address cannot be accessed at the same time due to triggers involved.</p>
  
  <p>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).</p>
  
  <p>Then, the above statement could be:</p>
  
  <pre><code>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;
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-12-21T16:43:57.363Z
Answers.Text: I would recommend also adding `and status = 'Added'` to your condition (to avoid matching contacts that were added then removed from a group).
Answers.UserDisplayName: bgm
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-12-18T12:25:02.700Z
Answers.LastActivityDate: 2015-12-18T12:25:02.700Z
Answers.UserDisplayName: Detlev Sieber
Answers.Body: <p>Here's an alternative formulation of Jon's query that might work better for you:
  <code>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');</code></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-06-16T16:41:32.770Z
Answers.Text: 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.
Answers.UserDisplayName: LunkRat
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-10-21T18:39:29.453Z
Answers.Text: I am getting the same problem. Why would this update civicrm_contact?
Answers.UserDisplayName: Josh
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-06-09T20:06:57.560Z
Answers.LastActivityDate: 2015-06-10T00:56:20.330Z
Answers.LastEditDate: 2015-06-10T00:56:20.330Z
Answers.UserDisplayName: Joe Murray
Answers.Body: <p>I solved this using the Drupal modules <a href="https://www.drupal.org/project/civicrm_entity">CiviCRM Entity</a> and <a href="https://www.drupal.org/project/views_bulk_operations">Views Bulk Operations</a>. </p>
  
  <p>This gave me a very powerful way to search, filter, and mass-modify the addresses in our database. </p>
  
  <ol>
  <li><p>Create a new view that shows CiviCRM Address entities and provides a page</p></li>
  <li><p>Add a <code>Bulk Operations: CiviCRM Address</code> field and configure it under "Selected bulk operations" to "Modify entity values" and
  select "Address Location Type" under its "Operations settings"</p></li>
  <li>Add a filter for <code>CiviCRM Address: Address Location Type</code> and set the location type that you want to change. Or expose the filter so you can filter at will. Refer to <code>civicrm/admin/locationType?reset=1</code>, hover the edit links to get the location ID from the URL</li>
  <li>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</li>
  </ol>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-08-27T19:57:09.863Z
Answers.Text: Great. Another good use for Entities and VBO. Can you 'tick' your answer to show it was accepted by you?
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-08-27T19:58:40.803Z
Answers.Text: Also, are you okay if I add this as another use case for civi and VBO to our blog here, http://www.fuzion.co.nz/tags/vbo as I would like to expand on how these Fuzion modules can be helpful to users?
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-08-27T20:15:43.467Z
Answers.Text: Yes, go ahead and use it. It seems to be a useful way of managing and cleaning up data.
Answers.UserDisplayName: LunkRat
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-09-01T17:06:50.483Z
Answers.Text: 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.
Answers.UserDisplayName: LunkRat
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-08-27T14:49:31.173Z
Answers.LastActivityDate: 2015-08-27T14:49:31.173Z
Answers.UserDisplayName: LunkRat
Body: <p>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?</p>
  
  <p>This is quite important, because when you have contacts with emails in different location types, <em>you can't dedupe on import</em>, see <a href="https://civicrm.stackexchange.com/questions/3620/import-from-csv-and-dedupe-using-email-match-to-contact-with-primary-email">Import from CSV and dedupe using Email (match to contact) with PRIMARY email?</a></p>
  
  <p>If I am wrong about this, please correct me!</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-06-13T08:13:02.933Z
Comments.Text: 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.
Comments.UserDisplayName: JoAnne
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-06-15T15:28:55.940Z
Comments.Text: 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.
Comments.UserDisplayName: LunkRat
Tags: <addresses><location-type>
Title: Change address location type for all contacts?
ThreadText: # Change address location type for all contacts?
  
  (Asked by: username_0 on 2015-06-08)
  
  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?](https://civicrm.stackexchange.com/questions/3620/import-from-csv-and-dedupe-using-email-match-to-contact-with-primary-email)
  
  If I am wrong about this, please correct me!
  
  **Tags:** addresses, location-type
  
  > **Comments:**
  > **username_1**: 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.  
  > **username_0**: 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.  
  
  ---
  
  ## 4 answers
  
  ### Answer by username_0 on 2015-08-27. Score: 6 | Accepted answer
  
  I solved this using the Drupal modules [CiviCRM Entity](https://www.drupal.org/project/civicrm_entity) and [Views Bulk Operations](https://www.drupal.org/project/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
  
  > **Comments:**
  > **username_8**: Great. Another good use for Entities and VBO. Can you 'tick' your answer to show it was accepted by you?  
  > **username_8**: Also, are you okay if I add this as another use case for civi and VBO to our blog here, http://www.username_8.co.nz/tags/vbo as I would like to expand on how these username_8 modules can be helpful to users?  
  > **username_0**: Yes, go ahead and use it. It seems to be a useful way of managing and cleaning up data.  
  > **username_0**: Hey @username_8, 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.  
  
  ---
  
  ### Answer by username_2 on 2015-06-08. Score: 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';
  
  ```
  
  > **Comments:**
  > **username_0**: 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.`  
  > **username_0**: Can anyone reproduce these SQL errors? Any ideas on where I should look to figure out why the queries suggested by Jon G and @username_6 fail with this error?  
  > **username_2**: @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  
  > **username_3**: Using a temporary table should do the trick and it's easier than delete / rebuild trigger.  
  
  ---
  
  ### Answer by username_4 on 2015-12-18. Score: 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;
  
  ```
  
  > **Comments:**
  > **username_5**: I would recommend also adding `and status = 'Added'` to your condition (to avoid matching contacts that were added then removed from a group).  
  
  ---
  
  ### Answer by username_6 on 2015-06-09. Score: 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');`
  
  > **Comments:**
  > **username_0**: 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.  
  > **username_7**: I am getting the same problem. Why would this update civicrm_contact?  
  
  ---
  
  