UPDATE scores
SET ranking = sub.ran
FROM (
SELECT subject_code,
    RANK() OVER(PARTITION BY subject_code ORDER BY score DESC) AS ran
    FROM scores
    GROUP BY subject_code
) AS sub
WHERE scores.subject_code = sub.subject_code;