/*
change_provo_to_levi.sql   Jerry Bryan   12 Feb 2026

Changes the string "Provo, " to the string "Levi, " in
SourceTable.Fields for any source based on the
source template named "Ancestry Record". 

The data is expected to be found in the variable Publish Place.
However, no attempt is made to parse SourceTable.Fields. Rather,
it is assumed that the string "Provo, " is unique to do
the replace without parsing.

SourceTable.Fields is updated rather than CitationTable.Fields
because Publish Place is a source variable rather than a
citation variable.

GLOB is used rather than LIKE as a quick way to make the
compare case sensitive.

The script only updates sources based on the "Ancestry Record"
template which contain the exact string "Provo, " in
SourceTable.Fields. That should serve to prevent stray updates
of sources based on other templates. It also means that after
the script has been run once, running it again will take no further
action.

*/

UPDATE SourceTable
  SET Fields = (
                SELECT CAST(REPLACE(CAST(S.Fields AS TEXT),'Provo,','Levi,') AS BLOB)
                FROM SourceTable AS S WHERE S.SourceID = SourceTable.SourceID
                )
  WHERE SourceTable.SourceID IN
  (
     SELECT S.SourceID
     FROM SourceTable AS S
     JOIN SourceTemplateTable AS ST ON ST.TemplateID = S.TemplateID
     WHERE ST.Name GLOB 'Ancestry Record'
       AND CAST(S.Fields AS TEXT) GLOB '*Provo,*'
  )