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