MediaTags – Delete Personal Having Fact Duplicates #media

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.

MediaTags-DeletePersonalHavingFactDupes.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
);