This should do the trick.

WITH CTE_rownumbers AS

(

SELECT Agreement_Area_Id, RID = ROW_NUMBER() OVER(ORDER BY Agreement_Area_Id DESC)

FROM [dbo].[Agreement_Area]

)

UPDATE aa

SET Agreement_Area_Number = cte.RID

FROM [dbo].[Agreement_Area] aa

JOIN CTE_rownumbers cte ON aa.Agreement_Area_Id = cte.Agreement_Area_Id;