UPDATE scores AS s1
SET ranking = sub.ranking
FROM (
    SELECT
        s2.student_code,
        s2.subject_code,
        COUNT(*) AS ranking
    FROM scores AS s2
    JOIN scores AS s3 ON s2.subject_code = s3.subject_code AND s2.score < s3.score
    GROUP BY s2.student_code, s2.subject_code
) AS sub
WHERE s1.student_code = sub.student_code
    AND s1.subject_code = sub.subject_code;