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