CiviCRM Community Forums (archive)

Hello thanks for reading --  as our database has grown, we have three used addresses for our contacts:

1) Work
2) Home
3) Other

Over time, some contacts have coded their Work to be primary, and some Home, and some Other.  We would like to reset all contacts so that Work is checked as the "primary"?

I am familiar with MySQL and can run queries if this is the easiest way, or if there is a way to do it through the Admin interface that's fine too.

Please let m know how I could accomplish this.

Thanks,
Stuart

Many contacts have both a Work and Home address.  Some have 3: Work, Home, Other.  The primary could be marked Work, Home, or Other.  It's all mixed up.

How can I set them all to "Work" as primary, without doing it manually 200+ times?

Any ideas?  Thanks!!!!!!!! 

Hi thanks for the information.

Here are the queries I used just so hopefully it can help someone else.

Step 1:
Identify the "location_type_id" of the location you want to use for the primary for everyone.  For me, it was 2.  So if you are using these queries use whatever location_type_id# is appropriate for you and substitute the number in instead of 2.

Step 2:
Run each of these queries on the following tables: civicrm_address, civicrm_phone, civicrm_im, civicrm_email, civicrm_openid
this query sets all the non-desired location_type_id to 0 or not primary
UPDATE civicrm_address SET is_primary=0 WHERE location_type_id<>2;
this query sets all the desired location_type_id to 1 or primary
UPDATE civicrm_address SET is_primary=1 WHERE location_type_id=2;

Step 3
And finally, since a small number of our contacts did not have a work address at all (id #2 in my case), and therefore had no primary address after the above queries were run -- this query identified those people by name and contact_id.  Because we had only a dozen of these situations in our whole database we decided to deal with this manually, although if you wanted you could use the following query as a subquery in a larger update query;

SELECT DISTINCT a.contact_id, c.first_name, c.last_name FROM civicrm_address AS a JOIN civicrm_contact AS c ON a.contact_id=c.id where (SELECT contact_id FROM civicrm_address AS b WHERE location_type_id=2 AND a.contact_id=b.contact_id) IS NULL AND a.contact_id IS NOT NULL AND c.last_name IS NOT NULL;