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