-- RM5copyMetadataToRM.sql
-- generates an UPDATE statement for each blank caption in RM for which there is a caption extracted from the image file
-- requires RM-Media.db3 with the RM5 database ATTACHed
SELECT 'UPDATE MultiMediaTable SET Caption=(SELECT Caption FROM PicasaTable WHERE ROWID=' || PicID ||') WHERE MediaID=' || MediaID ||';'
AS Statement
FROM
(
SELECT MediaID, Pic.ROWID AS PicID 
FROM MultiMediaTable AS RM 
INNER JOIN PicasaTable AS Pic 
ON REPLACE(RM.MediaPath || RM.MediaFile, '\', '/')
LIKE Pic.MediaPath || '/' || Pic.MediaFile
WHERE Pic.Caption NOT LIKE '-' AND RM.Caption IS NULL
)
;