CODEWITHSUNDEEP
mysqlsql
eagle
eagle

Reputation: 35

How to update a table with sum of values from two other tables in MySQL

I have three tables created in MySQL. As shown below Ap2 With fields StudentID and Total_Score

Term3 With fields StudentID and Total_Score

Term4 With fields StudentID and Total_Score

I am trying to SUM(Total_Score) in Term3 and add it to the SUM(Total_Score) in Term4 and update the Total_Score in ap2 with the sum of Term3.Total_Score and Term4.Total_Score. i.e ap2.Total_Score = Term3.Total_Score + Term4.Total_Score. I have tried to achieve this with the code below but MySQL is saying 0 rows affected, even though the SQL statement gives no errors. I need assistance as to what I should do. See my code below.

UPDATE ap2 AS c
LEFT JOIN (
SELECT StudentID, SUM(Total_Score) AS TotalScoret3
FROM term3
GROUP BY StudentID
) AS p ON c.StudentID = p.StudentID
LEFT JOIN (
SELECT StudentID, SUM(Total_Score) AS TotalScoret4
FROM term4
GROUP BY StudentID
) AS l ON c.StudentID = l.StudentID
SET Total_Score = COALESCE(TotalScoret3, 0) + COALESCE(TotalScoret4, 0)

Upvotes: 0

Views: 78

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I suspect you need to insert the rows, rather than update them. For this, you can use on duplicate key with an insert:

INSERT INTO ap2 (studentid, total_score)
    SELECT StudentID, SUM(Total_Score)
    FROM ((SELECT StudentID, Total_Score
           FROM term3
          ) UNION ALL
          (SELECT StudentID, Total_Score
           FROM term4
          ) 
         ) t
    GROUP BY StudentID
    ON DUPLICATE KEY UPDATE total_score = VALUES(total_score);

Note that the most recent versions of MySQL prefer a somewhat different syntax, but this will work for a while.

For this to work, you need ap2(studentId) needs to be the primary key or defined to be unique:

alter table ap2 add constraint unq_ap2_studentid unique(studentId);

Upvotes: 1

Akina
Akina

Reputation: 42622

As I understand the task you need in:

REPLACE INTO Ap2 (StudentID, Total_Score)
SELECT StudentID, SUM(Total_Score)
FROM (SELECT StudentID, Total_Score
      FROM Term3 
      UNION ALL
      SELECT StudentID, Total_Score
      FROM Term4) subquery
GROUP BY StudentID;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b13bec3b139f137c3143c067ff27a4d8

Ap2(StudentID) must be unique (maybe, primary key).

Upvotes: 0

Related Questions