UPDATE scores
SET ranking = sub.r
from (select student_code,subject_code,RANK() OVER(PARTITION BY subject_code ORDER BY score DESC) as r from scores) as sub
where scores.student_code = sub.student_code
and scores.subject_code = sub.subject_code
;

--select * from scores;