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