UPDATE scores AS s
SET ranking = sub.ranking
FROM(SELECT
     student_code,
     RANK() OVER(PARTITION BY subject_code ORDER BY ranking DESC) AS ranking
     FROM scores) AS sub;
     
     
     
SELECT *
FROM scores;