Media – Delete Unused #media #delete

Deletes records from MultiMediaTable untagged by any Person, Family, Fact, Place, Source or Citation. Was formerly included in Delete Phantoms.

MediaDeleteUnused.sql

-- MediaDeleteUnused.sql
/*
2013-01-21 Tom Holden ve3meo
 
Deletes records from MultiMediaTable untagged by
any Person, Family, Fact, Place, Source or Citation.
 
Extracted from DeletePhantoms.sql
*/
DELETE
FROM MultimediaTable
WHERE MediaID NOT IN (
        -- Person media
        SELECT MediaID
        FROM MediaLinkTable
        WHERE OwnerType = 0
            AND OwnerID IN (
                SELECT PersonID
                FROM PersonTable
                )
 
        UNION
 
        -- Family media
        SELECT MediaID
        FROM MediaLinkTable
        WHERE OwnerType = 1
            AND OwnerID IN (
                SELECT FamilyID
                FROM FamilyTable
                )
 
        UNION
 
        -- Event Media
        SELECT MediaID
        FROM MediaLinkTable
        WHERE OwnerType = 2
            AND OwnerID IN (
                SELECT EventID
                FROM EventTable
                )
 
        UNION
 
        -- Master Source Media
        SELECT MediaID
        FROM MediaLinkTable
        WHERE OwnerType = 3
            AND OwnerID IN (
                SELECT SourceID
                FROM SourceTable
                )
 
        UNION
 
        -- Citation Media
        SELECT MediaID
        FROM MediaLinkTable
        WHERE OwnerType = 4
            AND OwnerID IN (
                SELECT CitationID
                FROM CitationTable
                )
 
        UNION
 
        -- Place Media
        SELECT MediaID
        FROM MediaLinkTable
        WHERE OwnerType = 5
            AND OwnerID IN (
                SELECT PlaceID
                FROM PlaceTable
                )
        );