UPDATE scores SET ranking = rank_table.rank
FROM (SELECT 
            subject_code, 
            student_code,
            RANK() OVER(PARTITION BY subject_code ORDER BY score DESC) as rank 
        FROM scores) AS rank_table
WHERE scores.subject_code = rank_table.subject_code AND scores.student_code = rank_table.student_code