--Define the Selection criteria
DECLARE @pathStart varchar(50);
SELECT @pathStart = 'http://nacc-az-smpp:9091/v1/';

--Create a TMP table using the DOCID, existing moniker, and the new moniker
--NOTE: Test without the "INTO TMP_Moniker" until you are sure the "new" moniker is correct
SELECT DOCID, MONIKER as old, Replace(MONIKER,'/nacc-az-smpp/','/nacc-az-indx01/') as new INTO TMP_Moniker FROM EXTREFERENCES WHERE SUBSTRING(MONIKER,1,len(@pathStart)) = @pathStart;

--Join EXTREFERENCES to TMP_Moniker on the DOCID and update with the "new" value
UPDATE EXTREFERENCES
SET MONIKER = tmp.new
FROM EXTREFERENCES
INNER JOIN TMP_Moniker tmp ON EXTREFERENCES.DOCID = tmp.DOCID;

/*
NOTES:
- If for any reason this needs to be reverted, it is very easy to do so by running the update again with the "old" column.
- This query only affects a subset of records. It protects against an accidental replacement in unrelated records
- Using the variable for the selection and SUBSTRING is much more effcient than using LIKE
- If the tables are very large, you can create an index on the tmp table on the DOCID column
*/