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