UPDATE total_scores AS a
SET total_score = sub.sum
FROM (
SELECT student_code, SUM(score) AS sum
FROM scores
GROUP BY student_code
) AS sub
WHERE a.student_code = sub.student_code