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