update total_scores as ts
set ranking = sub.ranking
from (select test_code,
            student_code,
            rank() over(order by total_score desc) as ranking
    from total_scores 
    where test_code = 'T00001') as sub
where ts.test_code = sub.test_code
and ts.student_code = sub.student_code