WITH ranks AS (
    SELECT
        test_code,
        student_code, 
        RANK() OVER(ORDER BY total_score DESC) AS ranking
    FROM
        total_scores
)


UPDATE
    total_scores as ts
SET
    ranking = ranks.ranking
FROM
    ranks
WHERE
    ts.test_code = ranks.test_code
    AND
    ts.student_code = ranks.student_code;