UPDATE total_scores AS t
SET total_score = sub.total_score
FROM (
select s.test_code AS test_code, s.student_code AS student_code, sum(s.score) AS total_score from scores AS s
group by s.test_code, s.student_code
) AS sub
WHERE sub.student_code = t.student_code and sub.test_code = t.test_code