PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Sunday, October 23, 2022

[FIXED] How to update value of a column based on sum of same column values of other records in MYSQL?

 October 23, 2022     mysql, sql, sql-update     No comments   

Issue

I apologize if this is a basic question.

I have a table results like this :

enter image description here

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:

enter image description here

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

Any help is appreciated. Thank you.


Solution

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

eg

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';


Answered By - P.Salmon
Answer Checked By - Clifford M. (PHPFixing Volunteer)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing