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