We have a database where some memberships were attached to Households and some to Individuals. We'd like to normalize this -- what is the best way to move the membership from one to another? (e.g. take a Household's membership and move it to an Individual)
Future searchers, here is the query I used. Your mileage may vary as this was helpful for our particular case but you may need to tweak to make it work in yours. This makes no attempt to look at membership-related contributions or anything else, simply swaps membership owner ID.
In our case we had Households that owned the membership and shared it to Individuals by relationship. This code finds all Current, Primary Household-owned memberships that also have secondary relationships, then swaps the individual's contact ID into the primary membership's owner ID field.
UPDATE civicrm_membership primary_memb -- Join to Household Contact INNER JOIN civicrm_contact hh ON primary_memb.contact_id = hh.id -- Join to Individual (secondary) membership by primary membership ID INNER JOIN civicrm_membership indiv_memb ON indiv_memb.owner_membership_id = primary_memb.id -- Join to Individual Contact INNER JOIN civicrm_contact indiv ON indiv.id = indiv_memb.contact_id -- Change owner of primary membership SET primary_memb.contact_id = indiv.id -- Status ID of 2 means "Current" WHERE primary_memb.status_id = 2 -- Make sure it is a primary membership AND primary_memb.owner_membership_id IS NULL -- Only do this for the Household-owned memberships AND hh.contact_type = 'Household';
Next step for us is to purge all secondary/shared memberships and then regenerate them.