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;