UPDATE SCORES AS A1
SET RANKING = T1.RANK
FROM
(SELECT TEST_CODE
    ,STUDENT_CODE
    ,SUBJECT_CODE
    ,SCORE
    ,RANK() OVER(PARTITION BY SUBJECT_CODE ORDER BY SCORE DESC) AS RANK
FROM SCORES ) AS T1
WHERE 
T1.TEST_CODE = A1.TEST_CODE
AND T1.STUDENT_CODE = A1.STUDENT_CODE
AND T1.SUBJECT_CODE = A1.SUBJECT_CODE
AND T1.TEST_CODE = 'T00001';

SELECT *
FROM SCORES
ORDER BY 
    STUDENT_CODE ASC
    ,SCORE DESC
    ,SUBJECT_CODE ASC