UPDATE total_scores as ts
SET total_score = sub.totalscore
FROM (select student_code, sum(score) as totalscore from scores group by student_code ) AS sub
WHERE ts.student_code = sub.student_code ;