UPDATE total_scores
SET total_score = sub.sum
FROM (select sum(score) as sum
from scores
group by student_code) AS sub
WHERE total_scores.student_code = sub.student_code -- 副問合せ結果との結合条件を指定