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

SELECT * FROM scores