select l.name, l.centroid_sref, l.created_by_id, min(l.id) as to_keep, array_agg(l.id) as full_list, count(l.id)
into temporary tofix
from locations l
join locations_websites lw on lw.location_id=l.id and lw.website_id=23 and lw.deleted=false
where l.deleted=false 
group by l.name, l.centroid_sref, l.created_by_id
having count(l.id)>1
order by count(l.id) desc;

select l.id as to_delete, l.name, tofix.to_keep
into temporary locids
from locations l
join tofix on tofix.full_list @> ARRAY[l.id] AND tofix.to_keep<>l.id;

update cache_occurrences s
set location_id = li.to_keep
from locids li
where li.to_delete=s.location_id;

update locations set deleted='t' where id in (select to_delete from locids);

drop table tofix;
drop table locids;