Try this:
UPDATE Tbl_Doc_Stock_Chem_Add_Mst
SET C_Code = NewNum
FROM
(
SELECT C_Code, 'C06315' + CONVERT(VARCHAR(MAX), ROW_NUMBER() OVER (ORDER BY c.name)) AS NewNum
FROM Tbl_Doc_Stock_Chem_Add_Mst c
) AS table1 WHERE table1.C_Code=Tbl_Doc_Stock_Chem_Add_Mst.C_Code;
select * from Tbl_Doc_Stock_Chem_Add_Mst
Submit an article or tip