SQL (mysql) query explanation for multiple row update using group by sub query

user_8544150 0
Viewed 38

I have 2 tables, countries and users

users

id country_id
1 1
2 1
3 1
4 2
5 2

countries

id monthly_count
1 0
2 0

I want to update the monthly_count column in countries table by counting rows in users table grouped by country_id in a single update query. So after updating the countries table will look like this:

countries

id monthly_count
1 3
2 2

The query I ended up with is:

UPDATE countries c 
SET c.monthly_count = (
    SELECT COUNT(u.country_id) FROM users u 
    WHERE u.country_id = c.id 
    GROUP BY u.country_id
);

The query is working as expected. It is updating the monthly_count column of every row in the countries table with corresponding correct count values for each country_id group from users table.

However, I am not sure how the query is working. The main question I have is, how the result of subquery is assigned to the correct row? Is the subquery executed one time and returning all the group by count values for each country_id at once or, is the sub query being executed each time for every row in countries table?

mysql sql
1 Answers

I would write this an update join:

UPDATE countries c
INNER JOIN
(
    SELECT country_id, COUNT(*) AS cnt
    FROM users
    GROUP BY country_id
) u
     ON u.country_id = c.id
SET c.monthly_count = u.cnt;

That being said, the monthly counts are aggregate data, and you might want to consider not doing this update or storing this data. So, I am happy with the following query or view:

SELECT c.id, COUNT(u.cnt) AS monthly_count
FROM countries c
LEFT JOIN users u
    ON u.country_id = c.id
GROUP BY c.id;
user_18632290
Related