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;
Advertisement
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;