update scores as a
set ranking = b.ranking
from
(
select
    student_code
    ,subject_code
    ,rank() over(partition by subject_code order by score desc) ranking
from
    scores
) as b
where
    a.student_code = b.student_code
    and a.subject_code = b.subject_code
;

select * from scores;