SugarCRM: MySQL script to update Account-Contact Relationships based on exact address match (i.e. Contact Primary Address fields match the Account Shipping Address perfectly)

sugar_account_contact_address_matching.sql

-- remove existing account/contact relationships for all records that we might update
update accounts_contacts ac
join (
  select contacts.id as contact_id,accounts.id as account_id from contacts
  join accounts on contacts.primary_address_street = accounts.shipping_address_street and
    contacts.primary_address_city = accounts.shipping_address_city and
    contacts.primary_address_state = accounts.shipping_address_state and
    contacts.primary_address_postalcode = accounts.shipping_address_postalcode
  where contacts.deleted=0 and accounts.deleted=0)
as tmp on tmp.account_id = ac.account_id and tmp.contact_id = ac.contact_id
set deleted=1,date_modified=now()
where deleted=0;

-- and recreate account/contact relationships matching on address fields
insert into accounts_contacts (contact_id,id,account_id,date_modified,deleted)
select distinct(contacts.id),UUID(),accounts.id as account_id,now(),0 
from contacts
join accounts on contacts.primary_address_street = accounts.shipping_address_street and
  contacts.primary_address_city = accounts.shipping_address_city and
  contacts.primary_address_state = accounts.shipping_address_state and
  contacts.primary_address_postalcode = accounts.shipping_address_postalcode
where contacts.deleted=0 and accounts.deleted=0;