WITH t1 AS (SELECT subject_code, student_code, RANK() OVER (PARTITION BY subject_code ORDER BY score DESC) me FROM scores)

UPDATE scores
SET ranking=t1.me
FROM t1
WHERE t1.subject_code = scores.subject_code
  AND t1.student_code = scores.student_code
  AND scores.test_code = 'T00001';