UPDATE scores AS T1
SET ranking = sub.ranking
    FROM(SELECT student_code,RANK() OVER(PARTITION BY subject_code ORDER BY score DESC) AS ranking
            FROM scores) AS sub
    WHERE T1.student_code = sub.student_code;