How to update values from a table using a NTILE function?

Question

Abstract: I am trying to rank these stocks factors by top quintile and bottom quintile to build a long/short portfolio.

Table:

Desired result:

Code used:

UPDATE MOMENTUM_Quintile 
SET [2006-12-30] = NTILE(5) OVER (ORDER BY [2006-12-30] DESC)
FROM MOMENTUM_Quintile

Error message:

Msg 4108, Level 15, State 1, Line 7
Windowed functions can only appear in the SELECT or ORDER BY clauses.

Answer 1

The error is clear,reason why you can't do this,has to do with Logical query processing .you can try below

 With cte
 as(
 select *, NTILE(5) OVER (order by [2006-12-30] desc) as ntile
from
MOMENTUM_Quintile
)
update cte 
set [2006-12-30]=ntile