Updating multiple fields based on JOIN of two tables in mysql

Question

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 ?

Tim Biegeleisen · Accepted Answer · 2018-03-31 15:33:44Z

1

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;

answered Mar 31, 2018 at 15:33

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Updating multiple fields based on JOIN of two tables in mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related