MediaTags+-+Delete+Personal+Having+Fact+Duplicates

It is possible to excessively tag a media item to a Person and his/her Facts/Events. If someone uses Facts - Split Shared to Individual AND MediaTags - Copy Shared Facts Media To Sharee Personal, there certainly will be real duplicate mediatags for each person, one is the tag for the newly created Individual event from the first script, the other is the workaround Personal tag generated by the second. A procedure is needed to delete the duplicate Personal ones. This script endeavours to do that, based on the unique combinations of MediaID and OwnerID of events having media tags; Personal media tags having the same combination are deleted.



code format="sql" -- MediaTags-DeletePersonalHavingFactDupes.sql /* 2012-12-28 Tom Holden ve3meo

Deletes Personal MediaTags (Person-General) for media having duplicate tags to an event for the same person.

-- SELECT LinkID, MediaID, OwnerID -- testing DELETE FROM MediaLinkTable WHERE OwnerType=0 AND MediaID || '.' || OwnerID IN ( -- build ordered list of event mediatags in form MediaID.PersonID SELECT DISTINCT MediaID || '.' || PersonID AS MediaPerson FROM ( -- LinkIDs for PersonID's having Indiv Event MediaTags SELECT DISTINCT LinkID, MediaID, E.OwnerID AS PersonID FROM MediaLinkTable ML INNER JOIN EventTable E ON ML.OwnerID = E.EventID AND ML.OwnerType=2 AND E.OwnerType = 0

UNION

-- LinkIDs for Husband PersonID's having Family Event MediaTags SELECT DISTINCT LinkID, MediaID, F.FatherID AS HusbandID FROM MediaLinkTable ML INNER JOIN EventTable E ON ML.OwnerID = E.EventID AND ML.OwnerType=2 AND E.OwnerType = 1 INNER JOIN FamilyTable F ON E.OwnerID = F.FamilyID

UNION

-- LinkIDs for Wife PersonID's having Family Event MediaTags SELECT DISTINCT LinkID, MediaID, F.MotherID AS WifeID FROM MediaLinkTable ML INNER JOIN EventTable E ON ML.OwnerID = E.EventID AND ML.OwnerType=2 AND E.OwnerType = 1 INNER JOIN FamilyTable F ON E.OwnerID = F.FamilyID ) ORDER BY MediaPerson ); code