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