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

select * from scores