USE COMMON
UPDATE COMPANY_DETAIL SET NextYearCode = 'NEW'
UPDATE COMPANY_DETAIL SET NextYearCode = T1.F1 FROM (SELECT CompCode AS F1, CompName AS F2, CompYear AS F3 FROM COMPANY_DETAIL) AS T1 WHERE T1.F2 = CompName AND (CONVERT(Varchar(10),Left(CompYear,4)+1)+'-'+CONVERT(Varchar(10),right(compyear,2)+1)) = T1.F3