SQL – Pulling column data from two different tables and using it

I’m trying to pull data from two different columns from separate tables to use in my ranking query. I’m trying to keep data from the left column “PVPRanking” and merging the rest, thus my RIGHT JOIN.

PVPScores – Has PVPWins, PVPLose, and PVPGiveUP. While PVPRanking has the rest.

I cant seem to properly use this data without getting errors like there are duplicate columns that exist.

I’ve been google searching for a while now and everything has left me rather stumped when trying it out. I’m hoping you guys can guide me to the answer.

Thank you for your time.

USE DNWorld
GO
WITH cte AS (
  SELECT *, RANK() OVER (ORDER BY (((PVPWin * 1) + ([Kill] * 0.2) + ((PvpExp * 0.002)) - ((PVPLose * 0.5) + (Death * 0.1) + (PvPGiveUp * 0.25))) DESC) AS rn
  FROM PvPRanking P
    RIGHT JOIN PvpScores G
    ON P.CharacterID = G.CharacterID

)
UPDATE cte
SET 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
  ELSE 17
END
WHERE rn <= 100;```

Answer

I was able to solve the problem by creating temporary tables and pulling that data instead.