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