Join two tables, run a calculation based on their columns, and set a column based on the results

Why does my query break after this? I’m essentially running calculations based off the columns of both tables and then setting their PVPLevel when rn is a certain value between 1 and 5000.

SET PVPRanking.PVPLevel = CASE

USE DNWorld
GO

WITH cte AS (
  SELECT PVPScores.PVPWin, PVPScores.PVPLose, PVPRanking.Death, PVPRanking.[Kill], PVPRanking.PVPExp,
RANK() OVER (ORDER BY (((PVPScores.PVPWin * 1) + (PVPRanking.[Kill] * 0.2) + (PVPRanking.PvpExp * 0.002)) - ((PVPScores.PVPLose * 0.5) + (PVPRanking.Death * 0.1) + (PVPScores.PvPGiveUp * 0.5))) DESC, PVPScores.CharacterID DESC ) AS rn
  FROM PvPRanking
INNER JOIN PVPScores
      ON PVPRanking.CharacterID = PVPScores.CharacterID

)
UPDATE cte
SET PVPRanking.PVPLevel = CASE
  WHEN rn = 1 THEN 26 -- #1 Rank Champion
  WHEN rn BETWEEN 2 AND 10 THEN 25 -- Reita
    WHEN rn BETWEEN 11 and 20 THEN 24 -- Warlord
    WHEN rn BETWEEN 21 and 30 then 23 -- Slayer
    WHEN rn BETWEEN 31 and 40 then 22 -- WATCH OUT
    WHEN rn BETWEEN 41 and 50 then 21 -- Royalty
    WHEN rn BETWEEN 51 and 60 then 20 -- Knight of Justice
    WHEN rn BETWEEN 61 and 70 then 19 -- Furious Fiend
    WHEN rn BETWEEN 71 and 85 then 18 -- Devil of PVP
    WHEN rn BETWEEN 86 and 100 then 17 -- God Of PvP
  WHEN rn BETWEEN 101 and 125 then 16 -- Death God
  WHEN rn BETWEEN 126 and 150 then 15 -- Master
  WHEN rn BETWEEN 151 and 175 then 14 -- Unpredictable Demon
  WHEN rn BETWEEN 176 and 250 then 13 -- Demon
  WHEN rn BETWEEN 251 and 300 then 12 -- Crazy Fiend
  WHEN rn BETWEEN 301 and 400 then 11 -- Fiend
  WHEN rn BETWEEN 401 and 500 then 10 -- Bloodthirsty
  WHEN rn BETWEEN 501 and 600 then 9 -- Kavalan
  WHEN rn BETWEEN 601 and 700 then 8 -- Keepo
  WHEN rn BETWEEN 701 and 800 then 7 -- Senior PvP Tactician
  WHEN rn BETWEEN 801 and 900 then 6 -- PvP Tactician
  WHEN rn BETWEEN 901 and 1000 then 5 -- Senior Apprentice
  WHEN rn BETWEEN 1001 and 1100 then 4 -- Apprentice
  WHEN rn BETWEEN 1101 and 1500 then 3 -- Lesser Novice
  WHEN rn BETWEEN 1501 and 2000 then 2 -- Novice
  WHEN rn BETWEEN 2000 and 5000 then 1 -- Egg

  ELSE 1

END
WHERE rn <= 5000;

Answer

It appears that you only need to include PVPLevel in the select list of the CTE:

WITH cte AS (
    SELECT ps.PVPWin, ps.PVPLose, pr.Death, pr.[Kill], pr.PVPExp, pr.PVPLevel,
           RANK() OVER (ORDER BY (((ps.PVPWin * 1) + (pr.[Kill] * 0.2) + (pr.PvpExp * 0.002)) - ((ps.PVPLose * 0.5) + (pr.Death * 0.1) + (ps.PvPGiveUp * 0.5))) DESC, ps.CharacterID DESC ) AS rn
    FROM PvPRanking pr
    INNER JOIN PVPScores ps ON pr.CharacterID = ps.CharacterID
)

Then, continue with your current update query as is. Note that the update itself could be simplified a bit, resting on that the range checks are always increasing from top to bottom:

UPDATE cte
SET PVPLevel = CASE WHEN rn = 1 THEN 26    -- #1 Rank Champion
                    WHEN rn <= 10 THEN 25  -- Reita
                    WHEN rn <= 20 THEN 24  -- Warlord
                    WHEN rn <= 30 THEN 23  -- Slayer
                    WHEN rn <= 40 THEN 22  -- WATCH OUT
                    WHEN rn <= 50 THEN 21  -- Royalty
                    WHEN rn <= 60 THEN 20  -- Knight of Justice
                    WHEN rn <= 70 THEN 19  -- Furious Fiend
                    WHEN rn <= 85 THEN 18  -- Devil of PVP
                    WHEN rn <= 100 THEN 17 -- God Of PvP
                    WHEN rn <= 125 THEN 16 -- Death God
                    WHEN rn <= 150 THEN 15 -- Master
                    WHEN rn <= 175 THEN 14 -- Unpredictable Demon
                    WHEN <= 250 THEN 13    -- Demon
                    WHEN <= 300 THEN 12    -- Crazy Fiend
                    WHEN <= 400 THEN 11    -- Fiend
                    WHEN <= 500 THEN 10    -- Bloodthirsty
                    WHEN <= 600 THEN 9     -- Kavalan
                    WHEN <= 700 THEN 8     -- Keepo
                    WHEN <= 800 THEN 7     -- Senior PvP Tactician
                    WHEN rn <= 900 THEN 6  -- PvP Tactician
                    WHEN rn <= 1000 THEN 5 -- Senior Apprentice
                    WHEN rn <= 1100 THEN 4 -- Apprentice
                    WHEN rn <= 1500 THEN 3 -- Lesser Novice
                    WHEN rn <= 2000 THEN 2 -- Novice
                    WHEN rn <= 5000 THEN 1 -- Egg
                    ELSE 1 END
WHERE rn <= 5000;