UPDATE total_scores
SET total_score = sub.sum
    ,ranking = rank(order by sub.sum desc) 
    --,ranking = ROW_NUMBER() OVER(order by sub.sum desc)
    ,passed = case when sub.sum > 80 THEN '合' ELSE '否' END
FROM (
    select student_code, SUM(score) as sum
    from scores
    group by student_code
) AS sub
WHERE total_scores.student_code = sub.student_code;

select * from total_scores