How to update a table with a list of values at a time?

Question

I have

update NewLeaderBoards set MonthlyRank=(Select RowNumber() (order by TotalPoints desc) from LeaderBoards) 

I tried it this way -

(Select RowNumber() (order by TotalPoints desc) from LeaderBoards) as NewRanks 
 update NewLeaderBoards set MonthlyRank = NewRanks

But it doesnt work for me..Can anyone suggest me how can i perform an update in such a way..

Answer 1

You need to use the WITH statement and a full CTE:

;With Ranks As
    (
    Select PrimaryKeyColumn, Row_Number() Over( Order By TotalPoints Desc ) As Num
    From LeaderBoards
    )
Update NewLeaderBoards
Set MonthlyRank = T2.Num
From NewLeaderBoards As T1
    Join Ranks As T2
        On T2.PrimaryKeyColumn = T1.PrimaryKeyColumn