Deleting duplicate owners_to_ethnicities records
Posted by mholmes on 08 May 2012 in Activity log
An unwanted side-effect from the way I've deduped owners was that I've ended up with duplicate records in the owners_to_ethnicities table (different primary keys, but the same owner and ethnicity). This is a quick way to fix that, which I've now implemented:
DELETE ote2 FROM owners_to_ethnicities AS ote1, owners_to_ethnicities AS ote2 WHERE ote1.ote_owner_id_fk = ote2.ote_owner_id_fk AND ote1.ote_ethnicity_id_fk = ote2.ote_ethnicity_id_fk AND ote2.ote_ote_id > ote1.ote_ote_id
Similar processes may have to be run on other linking tables. It's probably safer to allow the duplicate records to be created by the merge, then examine and de-dupe them, than it would be to make the de-dupe process itself, which is already complicated enough, more messy.