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