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