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;