with t as (select  student_code,rank() over(order by total_score desc) rnk from total_scores where test_code='T00001')
update total_scores  set ranking=(select t.rnk 
                                  from t where t.student_code=total_scores.student_code)
where total_scores.test_code='T00001'