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;
    
    select * from scores;