update scores as s
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 s.student_code = sub.student_code
    and s.subject_code = sub.subject_code