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