/*
update_city_directory.sql   Jerry Bryan  4/24/2024

Changes occurences of the City Directory fact to a year specific
fact named YYYY City Directory, e.g. "1901 City Directory" without
the quotes. The YYYY City Directory facts are expected already to
exist and are not created. If there is no YYYY City Directory fact
that matches the year for the current City Directory fact, then no
action is taken and the City Directory fact is left unchanged.

*/

UPDATE EventTable

SET EventType = 
(
SELECT FT.FactTypeID FROM FactTypeTable AS FT WHERE FT.Name LIKE SUBSTR(Date,4,4) || ' City Directory'  
)

WHERE EventType IN (SELECT FT.FactTypeID FROM FactTypeTable AS FT WHERE FT.Name LIKE 'City Directory')
             AND SUBSTR(Date,4,4) || ' City Directory' IN
            (SELECT FT.Name FROM FactTypeTable AS FT WHERE FT.Name LIKE SUBSTR(Date,4,4) || ' City Directory'  )