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 SC.student_code = sub.student_code AND SC.subject_code = sub.subject_code;