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