I know there are similar questions to this, but the answers are not quite what I have been looking for. I have two MySQL tables, I’ll say table1 and table2. The fields in table1 are StudentID , Grade and Total_Score. And the fields in table2 are StudentID ,Grade and Total_Score as well. What I want to do is SUM(table1.Total_Score) and add it to SUM(table2.Total_Score). So that Total_Score in table2 is accumulated with scores from table1.Total_Score. I have tried to achieve that with my code below but the scores in table2.Total_Score never accumulate. They stay the same, even though my code gave no error. See my code below.
update table2 o inner join ( SELECT op.StudentID, sum(ot.Total_Score) as Total_Score_ot sum(op.Total_Score) as Total_Score_op FROM table1 ot inner join table2 op on op.StudentID = ot.StudentID WHERE ot.Grade = 'Grade8' GROUP BY op.StudentID ) as o1 on o.StudentID = o1.StudentID SET Total_Score=Total_Score_op + Total_Score_ot
Hmmm . . . I don’t think you need a join in the subquery:
update table2 o inner join (select ot.StudentID, sum(ot.Total_Score) as Total_Score_ot from table1 ot where ot.Grade = 'Grade8' group by ot.StudentId ) ot using (studentid) set o.Total_Score = o.Total_Score + Total_Score_ot;