UPDATE scores
SET ranking = sub.rank
FROM (SELECT student_code,RANK() OVER(ORDER BY score DESC) AS rank FROM scores WHERE subject_code = "S00001") 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 WHERE subject_code = "S00002") 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 WHERE subject_code = "S00003") AS sub
WHERE scores.student_code=sub.student_code
AND subject_code = "S00003";