update scores
set ranking = s2.ranking
from(
    SELECT 
        student_code
        ,RANK() OVER(PARTITION BY subject_code ORDER BY score DESC) as ranking
    FROM 
        scores
) as s2
where scores.student_code = s2.student_code;

select * from scores