Media – Set Primary Photo for Persons #media #update

This query responds to a problem with an import from Family Historian via FTM-flavoured GEDCOM reported by Stewartrb in the RootsMagic Forums thread
GEDCOM import with media. The images linked to the person in Family Historian were ultimately tagged as such in RootsMagic but none was marked as the Primary photo. Consequently, none showed on the main screen nor in reports other than Scrapbooks. This query sets the last image-type media file added to the Gallery among those that have been tagged to a person as that person’s Primary photo, when none of the tags for that person have been so checked. It could be readily modified to be the last tag added to the person rather than the last imported mediafile or to be the first instead of the last.

MediaTags-SetPrimaryForPersons.sql

-- MediaTags-SetPrimaryForPersons.sql
/* 2013-09-23 ve3meo
Sets the last mediatag as the Primary photo for a person with 1 or more tags for image-type media, none of which are so checked.
*/
UPDATE MediaLinkTable
SET    IsPrimary = 1
WHERE  LinkID IN
       ( -- a list of the last LinkIDs for persons with image-type media tags, none of which marked as the Primary photo
       SELECT  LinkID
       FROM    ( SELECT  *
               FROM     ( SELECT    ML.LinkID        -- tag number
                                 , ML.OwnerID        -- RIN for person, given the constraint below
                                 , ML.IsPrimary      -- 1 if tag box checked "Primary photo for this person" else 0
                        FROM       MediaLinkTable ML -- media tags table
                                   INNER JOIN MultiMediaTable MM
                        USING      (MediaID)        -- mediafiles table
                        WHERE      ML.OwnerType = 0 -- person
                        AND        MM.MediaType = 1 -- image type
                        ORDER BY   ML.OwnerID       -- sort by ownerid then
                                 , ML.IsPrimary     -- isprimary, putting any tag for the person set as primary to the bottom of the list
                        )
               GROUP BY OwnerID -- only the last LinkID in the list for the person comes out
               )
       WHERE   NOT IsPrimary -- only the last LinkID for a person with tags, none marked primary
       );