UPDATE scores
SET ranking = SUB.RANKING FROM
(SELECT student_code, subject_code,
RANK() OVER(PARTITION BY subject_code ORDER BY score DESC) AS RANKING
FROM scores) AS SUB
WHERE scores.student_code = SUB.student_code
AND scores.subject_code = SUB.subject_code;