update scores as sc
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 (sc.student_code = sub.student_code)
and (sc.subject_code = sub.subject_code) ;