How to update value of a column based on sum of same column values of other records in MYSQL?-CodePudding

I apologize if this is a basic question.

I have a table results like this :

I have records for 100s of ids like this.

I want to update all the records which has Type 'ALL' with the sum of Marks of 'Maths' and 'Physics' belonging to same id and date.

Desired result:

I am thinking of using UPDATE clause, but not sure how to structure it logically.

Any help is appreciated. Thank you.

CodePudding user response：

Mysql multi table update manual page - https://dev.mysql.com/doc/refman/8.0/en/update.html

drop table if exists t;

CREATE TABLE T(ID INT,SUBJECT VARCHAR(10), MARK INT);

INSERT INTO T VALUES
(1,'ALL',NULL),(1,'AAA',1),(1,'BBB',2),
(2,'ALL',NULL),(2,'AAA',10),(2,'BBB',20);

UPDATE T 
JOIN
 (SELECT ID,SUM(MARK) MARK FROM T WHERE SUBJECT <> 'ALL' GROUP BY ID) S ON S.ID = T.ID
SET T.MARK = S.MARK
WHERE SUBJECT = 'ALL';

CodePudding user response：

Please take a look at this url: mySql with rollup. It uses the rollup modifier with which you can make the sum grouped by specific rows. Look at the examples given and feel free to ask again.