UPDATE scores
SET ranking = sub.rank
FROM (SELECT student_code,RANK() OVER(ORDER BY score DESC) AS rank FROM scores) AS sub
WHERE scores.student_code=sub.student_code
AND subject_code = "S00001";

UPDATE scores
SET ranking = sub.rank
FROM (SELECT student_code,RANK() OVER(ORDER BY score DESC) AS rank FROM scores) AS sub
WHERE scores.student_code=sub.student_code
AND subject_code = "S00002";

UPDATE scores
SET ranking = sub.rank
FROM (SELECT student_code,RANK() OVER(ORDER BY score DESC) AS rank FROM scores) AS sub
WHERE scores.student_code=sub.student_code
AND subject_code = "S00003"