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
     FROM scores) AS sub
WHERE 
s.student_code = sub.subject_code
AND
s.subject_code = sub.subject_code;