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