Answers.Body: <p>You can do this directly in the database by running the following update query:</p>
  <pre><code>UPDATE civicrm_contact,
    (civicrm_relationship r 
      LEFT JOIN civicrm_contact c ON c.id = r.contact_id_a) 
    LEFT JOIN civicrm_contact c1 ON c1.id = r.contact_id_b 
    SET c.employer_id = r.contact_id_b,
        c.organization_name = c1.organization_name 
    WHERE r.relationship_type_id = 5 
      AND c.contact_type = 'individual' 
      AND c.employer_id IS NULL 
      AND r.is_active = 1;
  </code></pre>
  <p>This will update the employer field for all individual contacts who have an active employee relationship and do not have an employer already set.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-07-03T12:53:08.060Z
Answers.LastActivityDate: 2020-10-09T10:46:22.513Z
Answers.LastEditDate: 2020-10-09T10:46:22.513Z
Answers.UserDisplayName: Ben
Answers.Body: <p>This is building on <a href="https://civicrm.stackexchange.com/a/31290/35">Ben's answer</a>, but should run substantially faster.</p>
  <pre><code>UPDATE civicrm_contact employeeContact                                             
    INNER JOIN civicrm_relationship r ON employeeContact.id = r.contact_id_a         
    INNER JOIN civicrm_contact employerContact ON employerContact.id = r.contact_id_b
      SET employeeContact.employer_id = r.contact_id_b,                              
          employeeContact.organization_name = employerContact.organization_name      
    WHERE r.relationship_type_id = 5                              
      AND employeeContact.contact_type = 'Individual'                                
      AND employeeContact.employer_id IS NULL                                        
      AND r.is_active = 1;                                                           
  </code></pre>
  <p>In my use-case I have several qualifying relationships, e.g. &quot;is staff at&quot; and &quot;is intern at&quot;, so I changed the <code>r.relationship_type_id = 5</code> for <code>r.relationship_type_id IN (x, y, z)</code>. You can get the <code>x, y, z</code> IDs from the <strong>Administer » Customise Data and Screens » Relationship Types</strong> table. Just hover your mouse over the View link and look at the <code>id=</code> URL parameter.</p>
  <p>Please note that this code only works if the Individual (employee) is on the A-side of the relationship.</p>
  <p>You could use the <a href="https://civicrm.org/extensions/sql-tasks-extension-configurable-recurring-tasks" rel="nofollow noreferrer">SQL Tasks</a> extension to automate this.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-10-09T11:02:52.190Z
Answers.LastActivityDate: 2020-10-09T11:10:54.933Z
Answers.LastEditDate: 2020-10-09T11:10:54.933Z
Answers.UserDisplayName: artfulrobot
Body: <p>I want to bulk update the Employer field for the Individual contacts who have an active relationship of "employee of". </p>
  
  <p>Even though we have many active employee relationships for individuals, the Employer field is blank.</p>
  
Tags: <contacts><relationships>
Title: How can I bulk update the contact employer based on the employee relationship?
ThreadText: # How can I bulk update the contact employer based on the employee relationship?
  
  (Asked by: username_0 on 2019-07-03)
  
  I want to bulk update the Employer field for the Individual contacts who have an active relationship of "employee of".
  
  Even though we have many active employee relationships for individuals, the Employer field is blank.
  
  **Tags:** contacts, relationships
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_0 on 2019-07-03. Score: 2 | Accepted answer
  
  You can do this directly in the database by running the following update query:
  UPDATE civicrm_contact,
    (civicrm_relationship r 
      LEFT JOIN civicrm_contact c ON c.id = r.contact_id_a) 
    LEFT JOIN civicrm_contact c1 ON c1.id = r.contact_id_b 
    SET c.employer_id = r.contact_id_b,
        c.organization_name = c1.organization_name 
    WHERE r.relationship_type_id = 5 
      AND c.contact_type = 'individual' 
      AND c.employer_id IS NULL 
      AND r.is_active = 1;
  
  This will update the employer field for all individual contacts who have an active employee relationship and do not have an employer already set.
  
  
  ---
  
  ### Answer by username_1 on 2020-10-09. Score: 2
  
  This is building on username_0's answer, but should run substantially faster.
  UPDATE civicrm_contact employeeContact                                             
    INNER JOIN civicrm_relationship r ON employeeContact.id = r.contact_id_a         
    INNER JOIN civicrm_contact employerContact ON employerContact.id = r.contact_id_b
      SET employeeContact.employer_id = r.contact_id_b,                              
          employeeContact.organization_name = employerContact.organization_name      
    WHERE r.relationship_type_id = 5                              
      AND employeeContact.contact_type = 'Individual'                                
      AND employeeContact.employer_id IS NULL                                        
      AND r.is_active = 1;                                                           
  
  In my use-case I have several qualifying relationships, e.g. "is staff at" and "is intern at", so I changed the r.relationship_type_id = 5 for r.relationship_type_id IN (x, y, z). You can get the x, y, z IDs from the Administer » Customise Data and Screens » Relationship Types table. Just hover your mouse over the View link and look at the id= URL parameter.
  Please note that this code only works if the Individual (employee) is on the A-side of the relationship.
  You could use the SQL Tasks extension to automate this.
  
  
  ---
  
  