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