CODEWITHSUNDEEP
mysql
HopeKing
HopeKing

Reputation: 3503

Updating multiple fields based on JOIN of two tables in mysql

I am trying to merge the below 2 Update statements into a single query, since they are both working on the same tables.

My school.present count is updated based on count of children.attendance=PRESENT status 

UPDATE school s SET s.present=(SELECT count(children.id) FROM children c 
WHERE c.school_id =s._id  AND c.attendance='PRESENT')

Similarly, school.danced count is updated based on count of children.activity=DANCE status 

UPDATE school s SET s.danced=(SELECT count(children.id) FROM children c 
WHERE c.school_id =s._id  AND c.activity='DANCE')

Is it possible to merge the two to save on the number of queries ?

My current attempt is merge the two into the following - but I think this will result in the same two queries as above

UPDATE school s SET 
s.danced=(SELECT count(children.id) FROM children c 
WHERE c.school_id =s._id  AND c.activity='DANCE'),  
s.present=(SELECT count(children.id) FROM children c 
WHERE c.school_id =s._id  AND c.attendance='PRESENT')

Is there a better way to achieve this or is the above as efficient as it can get ?

Upvotes: 1

Views: 33

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520938

Use an update join containing both aggregations:

UPDATE school s
INNER JOIN
(
    SELECT
        school_id,
        COUNT(CASE WHEN attendance = 'PRESENT' THEN 1 END) AS p_cnt,
        COUNT(CASE WHEN attendance = 'DANCE'   THEN 1 END) AS d_cnt
    FROM children
    GROUP BY school_id
) c
    ON s._id = c.school_id
SET
    present = c.p_cnt,
    danced = c.d_cnt;

Upvotes: 1

Related Questions