Because of a insufficient training re the importance of setting the practice location when creating a new customer, we have ended up with a significant number of customers having the wrong practice location.

Hence I needed to do a bulk update to change the customer's practice location. Specifically, for all customers with a given practice location and a given account type, I wanted to change the practice location.

The SQL I used is below.  Regards, Tim G

/* test customer selection */
select e.name as customer, la.code as acType, pl.name as practiceLoc from entities e
left join entity_links el on el.source_id = e.entity_id and el.arch_short_name = 'entityLink.customerLocation'
left join entities pl on pl.entity_id = el.target_id
join entity_classifications ec on ec.entity_id = e.entity_id
join lookups la on la.lookup_id = ec.lookup_id and la.arch_short_name = 'lookup.customerAccountType'
where e.arch_short_name = 'party.customerperson'
and la.code = 'CREATURE_COMFORTS'
and pl.name='Creature Comforts Office';

/* test entity link selection */
select e.name as customer, la.code as acType, pl.name as practiceLoc, pl.entity_id as pLoc_id
from entity_links el
join entities e on el.source_id = e.entity_id and e.arch_short_name = 'party.customerperson'
left join entities pl on pl.entity_id = el.target_id
join entity_classifications ec on ec.entity_id = e.entity_id
join lookups la on la.lookup_id = ec.lookup_id and la.arch_short_name = 'lookup.customerAccountType'
where el.arch_short_name = 'entityLink.customerLocation'
and la.code = 'CREATURE_COMFORTS'
and pl.name='Creature Comforts Office';

/* get locations & account types */
select * from entities pl where pl.arch_short_name = 'party.organisationLocation';
select * from lookups l where l.arch_short_name = 'lookup.customerAccountType';

/* set Customer location to Creature Comforts (160) for all customers with loc Creature Comforts Office and account type Creature Comforts */
update entity_links el
join entities e on el.source_id = e.entity_id and e.arch_short_name = 'party.customerperson'
left join entities pl on pl.entity_id = el.target_id
join entity_classifications ec on ec.entity_id = e.entity_id
join lookups la on la.lookup_id = ec.lookup_id and la.arch_short_name = 'lookup.customerAccountType'
set el.target_id =160
where el.arch_short_name = 'entityLink.customerLocation'
and la.code = 'CREATURE_COMFORTS'
and pl.name='Creature Comforts Office';