UPDATE file_version AS fv
   SET location = REPLACE(fv.location, '/datazone/campaign/bad/rootpath', '/datazone/blessed/rootpath')
  FROM file AS f
      ,dataset AS d
      ,catalog AS c
WHERE fv.file_id = f.id
  AND f.dataset_id = d.id
  AND d.name = c.dataset_name
  AND c.rootpath = 'datazone/campaign/bad/rootpath';

UPDATE file_version AS fv
   SET url = REPLACE(fv.url, 'datazone/campaign/bad/rootpath', 'datazone/blessed/rootpath')
  FROM file AS f
      ,dataset AS d
      ,catalog AS c
WHERE fv.file_id = f.id
  AND f.dataset_id = d.id
  AND d.name = c.dataset_name
  AND fv.url IS NOT NULL
  AND c.rootpath = 'datazone/campaign/bad/rootpath';

UPDATE catalog
   SET rootpath = 'datazone/blessed/rootpath'
 WHERE rootpath = 'datazone/campaign/bad/rootpath';
