UPDATE scores AS T1
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 T1.student_code = sub.student_code
        AND T1.subject_code= sub.subject_code;
SELECT*
FROM scores;