How to update one columns data using another tables data TSQL

The error is because your subquery is returning more than one record for the UPDATE. To fix this, you can do this using a JOIN with your UPDATE

UPDATE t1SET ForeignKey = t2.idFROM TableIWantToCorrect t1INNER JOIN TableWithIDs t2    ON UPPER(t2.CompareName) = UPPER(SUBSTRING(t1.Name, 4, 3))

 Update TableIWantToCorrect SET ForeignKey =  s.id FROM TableIWantToCorrect , TableWithIDs as s WHERE UPPER(s.CompareName) = UPPER( (SUBSTRING(TableIWantToCorrect.Name, 4, 3))

--CREATE FUNCTION dbo.ufn_FindReports --(@InEmpID INTEGER)--RETURNS @retFindReports TABLE --(--    EmployeeID int primary key NOT NULL,--    FirstName nvarchar(255) NOT NULL,--    LastName nvarchar(255) NOT NULL,--    JobTitle nvarchar(50) NOT NULL--)----Returns a result set that lists all the employees who report to the ----specific employee directly or indirectly.*/--AS--BEGIN--WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns--    AS (--        SELECT e.EmployeeID, e.ManagerID, p.FirstName, p.LastName, P.JobTitle, 0 -- Get the initial list of Employees for Manager n--        FROM HumanResources.Employee e --INNER JOIN Person.Person p --ON p.Employeeid = e.EmployeeID--        WHERE e.EmployeeID = @InEmpID--        UNION ALL--        SELECT e.EmployeeID, e.ManagerID, p.FirstName, p.LastName, P.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor--        FROM HumanResources.Employee e --            INNER JOIN EMP_cte--            ON e.ORGANIZATIONNODE.GetAncestor(1) = EMP_cte.OrganizationNode--INNER JOIN Person.Person p --ON p.Employeeid= e.EmployeeID--        )---- copy the required columns to the result of the function --   INSERT @retFindReports--   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel--   FROM EMP_cte --   RETURN--END;--GO>