SQL append data to table using dense_rank()

I’ve been trying to piece together bits of code that I’ve googled but haven’t had any luck. I’m trying to add a new column to an existing table. I can view the column using the below code from a select query:

I basically want to append the results from this select query to my original table.

Select  
    Dense_Rank() Over(Order By [Asset_Type],
    [Account],
    [Issuer],
    [Return_Bucket],
    [Age_Bucket],
    [Type_Bucket],
    [Risk_Bucket]) AS CohortID
 from Test_Table    

I’ve tried using a cte with the below code but I’ getting an error.

alter table Test_Table      
add CohortID float

With cte AS
(
    Select
    r = Dense_Rank() Over(Order By [Asset_Type],
        [Account],
        [Issuer],
        [Return_Bucket],
        [Age_Bucket],
        [Type_Bucket],
        [Risk_Bucket])
    from Test_Table 
)
UPDATE c
Set CohortID = r
FROM cte c;

Any thoughts or suggestions would be appreciated. Thanks!

Answer

Try this version:

alter table Test_Table add CohortID float;
GO

WITH toupdate AS (
      SELECT r = Dense_Rank() Over(Order By [Asset_Type], t.*
      FROM Test_Table t
     )
UPDATE toupdate
    SET CohortID = r;

I suspect the error with your query is that you did not include the new column in the column list in the CTE.