update scores as sc
set ranking = sub.ranking
from (select 
        test_code,
        student_code,
        rank() over(order by score desc) as ranking 
    from scores where test_code = 'T00001') as sub
where sc.student_code = sub.student_code
and sc.test_code = sub.test_code