UPDATE t1
SET ForeignKey = t2.id
FROM TableIWantToCorrect t1
INNER JOIN TableWithIDs t2
ON UPPER(t2.CompareName) = UPPER(SUBSTRING(t1.Name, 4, 3))
UPDATE t1
SET ForeignKey = t2.id
FROM TableIWantToCorrect t1
INNER JOIN TableWithIDs t2
ON UPPER(t2.CompareName) = UPPER(SUBSTRING(t1.Name, 4, 3))