Answers.Body: <p>If the households and individuals exist in the database but individuals do not have a shared address with the household, you can run the following sql to create a csv that you can import.</p>
  
  <p>On the import use update and link the first 7 columns to the appropriate columns.  (If you don't link the street address, city, state, etc., the individual's shown address will be whatever it was before).  The last two columns are just for sanity so you can double check the csv is going to update the right things.</p>
  
  <pre><code>select r.contact_id_a internal_id,
         ah.street_address,
         ah.city,
         p.name state,
         ah.postal_code,
         co.name country,
         ah.id master,
         ci.sort_name Ind_name,
         ch.sort_name hh_name
  from   civicrm_relationship r,
         civicrm_contact ci,
         civicrm_contact ch,
         civicrm_address ah,
         civicrm_country co,
         civicrm_state_province p
  where  r.contact_id_b = ah.contact_id
  and    r.contact_id_a = ci.id
  and    r.contact_id_b = ch.id
  and    r.relationship_type_id = 7  -- hh
  and    ah.master_id is null  -- make sure hh isn't mastered off ind
  and    ah.is_primary -- use the hh primary address
  and    ah.country_id = co.id
  and    ah.country_id = p.country_id
  and    p.id = ah.state_province_id
  and    not ci.is_deleted
  and    not ch.is_deleted
  and    not exists (select 'does individual have master address id'
              from   civicrm_address ci
              where  ci.contact_id = r.contact_id_a
              and    ci.master_id is not null)
  </code></pre>
  
  <p>If you did an import update and only linked the master address you fix the displayed address of the individuals with sql.</p>
  
  <p>Here are two queries.  One lists the people with a master address but missing the details.  The other fixes them.</p>
  
  <pre><code>select ca.street_address,
         ca.city,
         ca.state_province_id,
         ca.postal_code,
         ca.postal_code_suffix,
         ca.country_id,
         pa.street_address,
         pa.city,
         pa.state_province_id,
         pa.postal_code,
         pa.postal_code_suffix,
         ca.country_id
  from   civicrm_address ca,
         civicrm_address pa
  where  ca.master_id = pa.id
  and    ca.master_id is not null
  and    ca.street_address is null
  </code></pre>
  
  <p>Fix missing</p>
  
  <pre><code>update civicrm_address ca,
         civicrm_address pa
  set    ca.street_address = pa.street_address,
         ca.city = pa.city,
         ca.state_province_id = pa.state_province_id,
         ca.postal_code = pa.postal_code,
         ca.postal_code_suffix = pa.postal_code_suffix,
         ca.country_id = pa.country_id
  where  ca.master_id = pa.id
  and    ca.master_id is not null
  and    ca.street_address is null
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-06-18T14:44:29.737Z
Answers.Text: This is a great script!  This was very helpful, but the real problem was that during import, if the contact had an address, it didn't respect import of master address and update for some reason.  I managed repair using this script and some additional finesse
Answers.UserDisplayName: Sonicthoughts
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-06-18T14:46:21.637Z
Answers.Text: Would be great to have a script to 1) assign master (home) address of household to all household members. (and make primary) and 2) easy way to create spouses from households with 2 members.  At this point I'm going to use excel/import/output  but these scripts are a really great start for me - thx!
Answers.UserDisplayName: Sonicthoughts
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-06-17T17:30:17.507Z
Answers.LastActivityDate: 2015-06-18T18:27:17.587Z
Answers.LastEditDate: 2015-06-18T18:27:17.587Z
Answers.UserDisplayName: Paul-Tahoe
Answers.Body: <p>A workaround is to import the address along with the Master Address ID. So import like you are importing a new address but also include the Master Address ID, mapped to the "Master Address Belongs To" field.  It links them and populates the address.</p>
  
  <p><a href="https://i.sstatic.net/7uIPH.jpg" rel="nofollow noreferrer"><img src="https://i.sstatic.net/7uIPH.jpg" alt="Master address import shared address"></a></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-06-17T17:24:26.840Z
Answers.LastActivityDate: 2015-12-04T23:03:20.650Z
Answers.LastEditDate: 2015-12-04T23:03:20.650Z
Answers.UserDisplayName: Paul-Tahoe
Answers.Body: <p>This is in answer to the how to create spouses from household members.</p>
  
  <p>Here is some sql.   You can use it to create a csv to do an import update individual or paste into the Specify SQL Query box after selecting Specify SQL Query for data source.  </p>
  
  <p>You'll have to manually link the second column to Spouse Of Internal Contact ID.  The others should default correct.  The last three do-not-import columns are just for sanity sake to make sure things look correct.</p>
  
  <p>Notes about sql:  If the spouse relationship already exists it will skip that one.  If the household is made up of more than two individuals e.g. husband, wife, kid1, it will make two of them spouse and ignore the third.  Not sure which two will be used.</p>
  
  <pre><code>SELECT rh.contact_id_a internal_id,
         ri.contact_id_a 'Spouse of',  -- on import link to spouse of internal id
         ch.sort_name '-- Household',  -- do not import
         cs1.sort_name '-- Spouse1',   -- do not import
         cs2.sort_name '-- Spouse2'    -- do not import
  FROM   civicrm_contact ch,  -- household
         civicrm_contact cs1, -- first spouse
         civicrm_contact cs2, -- second spouse
         civicrm_relationship rh,  
         civicrm_relationship ri
  WHERE  ri.contact_id_b = rh.contact_id_b
  and    ri.relationship_type_id = 7
  and    rh.relationship_type_id = 7
  and    ri.contact_id_a &lt;&gt; rh.contact_id_a
  and    rh.contact_id_b = ch.id
  and    rh.contact_id_a = cs1.id
  and    ri.contact_id_a = cs2.id
  and not exists (select 'is there already a spouse relationship'
                  from   civicrm_relationship re
                  where  (rh.contact_id_a = re.contact_id_a
                     or   rh.contact_id_a = re.contact_id_b)
                  and     re.relationship_type_id = 2) -- spouse
  group by rh.contact_id_b  -- sneaky way to get only one row for each pair otherwise get two rows
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-06-18T23:06:38.390Z
Answers.LastActivityDate: 2015-06-18T23:06:38.390Z
Answers.UserDisplayName: Paul-Tahoe
Body: <p>I've seen several requests for this, but some seems outdated. 
  <strong>Goal: Import contacts and households and update existing records.</strong>  I'm using <a href="http://forum.civicrm.org/index.php?topic=29506.0" rel="nofollow">http://forum.civicrm.org/index.php?topic=29506.0</a> as a reference.  <strong>Should it work for contact updates?</strong></p>
  
  <ol>
  <li>Using 4.6.3 and import GUI.</li>
  <li>Import household contact with primary home address <strong>(complete)</strong></li>
  <li>Associate relationship for contacts as Member of household <strong>(complete)</strong></li>
  <li>Associate relationship for contacts of Spouse <strong>(TBD but suggestions welcome)</strong></li>
  <li>Associate contacts as having Master Address belonging to household</li>
  </ol>
  
  <p>This seems like a very common use case and I'm almost there...now attempting steps 4 and 5.</p>
  
  <p><strong>Status:</strong></p>
  
  <ul>
  <li>Created csv with household info and imported successfully.</li>
  <li>Used mysql to collect master address record id.</li>
  </ul>
  
  <p>When importing individuals:</p>
  
  <ul>
  <li>Selected Update method</li>
  <li>I selected "Master Address Belongs To..." and included the Address ID from mysql
  <strong>- Import successful but Address is not updated for existing contact addresses and does not belong to household</strong></li>
  </ul>
  
  <p>Could this be related to: <a href="https://issues.civicrm.org/jira/browse/CRM-16588" rel="nofollow">CRM-16588</a>?</p>
  
  <p>So now I have some related addresses (although I have to manually save to show them properly in view screen) but others belonging to individuals.  I suppose I could delete all the individual addresses and reimport but don't like that idea ....</p>
  
  <p>BTW perhaps this could drive a rather large feature request/refactor:
  <em>Rather than import master address belongs to ... use the same relationship method but enable an option like "Let Household fields override Household Member"  Perhaps this could work like relationship permissions.  This could mean any updates to a contact address would update the linked field (consistent with how event pricesets/address work...)</em></p>
  
Tags: <import><addresses><households>
Title: Importing Household Address if Already Exists
ThreadText: # Importing Household Address if Already Exists
  
  (Asked by: username_0 on 2015-06-17)
  
  I've seen several requests for this, but some seems outdated.
  **Goal: Import contacts and households and update existing records.** I'm using <http://forum.civicrm.org/index.php?topic=29506.0> as a reference. **Should it work for contact updates?**
  
  1. Using 4.6.3 and import GUI.
  2. Import household contact with primary home address **(complete)**
  3. Associate relationship for contacts as Member of household **(complete)**
  4. Associate relationship for contacts of Spouse **(TBD but suggestions welcome)**
  5. Associate contacts as having Master Address belonging to household
  
  This seems like a very common use case and I'm almost there...now attempting steps 4 and 5.
  
  **Status:**
  
  * Created csv with household info and imported successfully.
  * Used mysql to collect master address record id.
  
  When importing individuals:
  
  * Selected Update method
  * I selected "Master Address Belongs To..." and included the Address ID from mysql
    **- Import successful but Address is not updated for existing contact addresses and does not belong to household**
  
  Could this be related to: [CRM-16588](https://issues.civicrm.org/jira/browse/CRM-16588)?
  
  So now I have some related addresses (although I have to manually save to show them properly in view screen) but others belonging to individuals. I suppose I could delete all the individual addresses and reimport but don't like that idea ....
  
  BTW perhaps this could drive a rather large feature request/refactor:
  *Rather than import master address belongs to ... use the same relationship method but enable an option like "Let Household fields override Household Member" Perhaps this could work like relationship permissions. This could mean any updates to a contact address would update the linked field (consistent with how event pricesets/address work...)*
  
  **Tags:** import, addresses, households
  
  
  ---
  
  ## 3 answers
  
  ### Answer by username_1 on 2015-06-17. Score: 3 | Accepted answer
  
  If the households and individuals exist in the database but individuals do not have a shared address with the household, you can run the following sql to create a csv that you can import.
  On the import use update and link the first 7 columns to the appropriate columns.  (If you don't link the street address, city, state, etc., the individual's shown address will be whatever it was before).  The last two columns are just for sanity so you can double check the csv is going to update the right things.
  select r.contact_id_a internal_id,
         ah.street_address,
         ah.city,
         p.name state,
         ah.postal_code,
         co.name country,
         ah.id master,
         ci.sort_name Ind_name,
         ch.sort_name hh_name
  from   civicrm_relationship r,
         civicrm_contact ci,
         civicrm_contact ch,
         civicrm_address ah,
         civicrm_country co,
         civicrm_state_province p
  where  r.contact_id_b = ah.contact_id
  and    r.contact_id_a = ci.id
  and    r.contact_id_b = ch.id
  and    r.relationship_type_id = 7  -- hh
  and    ah.master_id is null  -- make sure hh isn't mastered off ind
  and    ah.is_primary -- use the hh primary address
  and    ah.country_id = co.id
  and    ah.country_id = p.country_id
  and    p.id = ah.state_province_id
  and    not ci.is_deleted
  and    not ch.is_deleted
  and    not exists (select 'does individual have master address id'
              from   civicrm_address ci
              where  ci.contact_id = r.contact_id_a
              and    ci.master_id is not null)
  
  If you did an import update and only linked the master address you fix the displayed address of the individuals with sql.
  Here are two queries.  One lists the people with a master address but missing the details.  The other fixes them.
  select ca.street_address,
         ca.city,
         ca.state_province_id,
         ca.postal_code,
         ca.postal_code_suffix,
         ca.country_id,
         pa.street_address,
         pa.city,
         pa.state_province_id,
         pa.postal_code,
         pa.postal_code_suffix,
         ca.country_id
  from   civicrm_address ca,
         civicrm_address pa
  where  ca.master_id = pa.id
  and    ca.master_id is not null
  and    ca.street_address is null
  
  Fix missing
  update civicrm_address ca,
         civicrm_address pa
  set    ca.street_address = pa.street_address,
         ca.city = pa.city,
         ca.state_province_id = pa.state_province_id,
         ca.postal_code = pa.postal_code,
         ca.postal_code_suffix = pa.postal_code_suffix,
         ca.country_id = pa.country_id
  where  ca.master_id = pa.id
  and    ca.master_id is not null
  and    ca.street_address is null
  
  > **Comments:**
  > **username_0**: This is a great script!  This was very helpful, but the real problem was that during import, if the contact had an address, it didn't respect import of master address and update for some reason.  I managed repair using this script and some additional finesse  
  > **username_0**: Would be great to have a script to 1) assign master (home) address of household to all household members. (and make primary) and 2) easy way to create spouses from households with 2 members.  At this point I'm going to use excel/import/output  but these scripts are a really great start for me - thx!  
  
  ---
  
  ### Answer by username_1 on 2015-06-17. Score: 3
  
  A workaround is to import the address along with the Master Address ID. So import like you are importing a new address but also include the Master Address ID, mapped to the "Master Address Belongs To" field. It links them and populates the address.
  
  [![Master address import shared address](https://i.sstatic.net/7uIPH.jpg)](https://i.sstatic.net/7uIPH.jpg)
  
  
  ---
  
  ### Answer by username_1 on 2015-06-18. Score: 1
  
  This is in answer to the how to create spouses from household members.
  Here is some sql.   You can use it to create a csv to do an import update individual or paste into the Specify SQL Query box after selecting Specify SQL Query for data source.  
  You'll have to manually link the second column to Spouse Of Internal Contact ID.  The others should default correct.  The last three do-not-import columns are just for sanity sake to make sure things look correct.
  Notes about sql:  If the spouse relationship already exists it will skip that one.  If the household is made up of more than two individuals e.g. husband, wife, kid1, it will make two of them spouse and ignore the third.  Not sure which two will be used.
  SELECT rh.contact_id_a internal_id,
         ri.contact_id_a 'Spouse of',  -- on import link to spouse of internal id
         ch.sort_name '-- Household',  -- do not import
         cs1.sort_name '-- Spouse1',   -- do not import
         cs2.sort_name '-- Spouse2'    -- do not import
  FROM   civicrm_contact ch,  -- household
         civicrm_contact cs1, -- first spouse
         civicrm_contact cs2, -- second spouse
         civicrm_relationship rh,  
         civicrm_relationship ri
  WHERE  ri.contact_id_b = rh.contact_id_b
  and    ri.relationship_type_id = 7
  and    rh.relationship_type_id = 7
  and    ri.contact_id_a <> rh.contact_id_a
  and    rh.contact_id_b = ch.id
  and    rh.contact_id_a = cs1.id
  and    ri.contact_id_a = cs2.id
  and not exists (select 'is there already a spouse relationship'
                  from   civicrm_relationship re
                  where  (rh.contact_id_a = re.contact_id_a
                     or   rh.contact_id_a = re.contact_id_b)
                  and     re.relationship_type_id = 2) -- spouse
  group by rh.contact_id_b  -- sneaky way to get only one row for each pair otherwise get two rows
  
  
  ---
  
  