How to manipulate the output after selecting and ordering TOP RESULT

USE DNWorld
GO

SELECT TOP (100) WITH TIES PvPExp 
FROM PvPRanking
ORDER BY PVPExp DESC

UPDATE PVPRanking
SET PVPLevel = 26 
WHERE TOP(1) = 1

ERROR:

Msg 156, Level 15, State 1, Server NS544979, Procedure , Line 0 Incorrect syntax near the keyword ‘TOP’. [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword ‘TOP’. (156)

I have successfully selected and ordered the PvPExp in the query.

I want to set PVPLEVEL = 26 for the #1 Top result. That isn’t working.

Then from that I want to set The #2-#5 Top result a certain value.

So on and so forth. Please help

Answer

With RANK() window function:

WITH cte AS (
  SELECT *, RANK() OVER (ORDER BY PVPExp DESC) AS rn
  FROM PvPRanking
)
UPDATE cte
SET PVPLevel = CASE rn
  WHEN 1 THEN 26
  ELSE ?
END  
WHERE rn <= 5

Replace ? with the value that you want to set to #2-#5 Top rows.
If you want to set different values for them also:

.............................
SET PVPLevel = CASE rn
  WHEN 1 THEN 26
  WHEN 2 THEN ?
  WHEN 3 THEN ?
  WHEN 4 THEN ?
  WHEN 5 THEN ?
END 
.............................