MySQL: Count records from one table and then update another

Question

Got 2 tables / entities, very straightforward scenario.

Table poets - Columns: id, poet, nation

Table nations - Columns: id, nation, count

Basically, nations to poets has a mapping of one to many, naturally. For example, there are 1000 poets from 60 nations. Each poet in poets is assigned to a nation by the nation field which contains the id of one of the nations in nations.

The count field of nations contains the number of poets in poets from this nation.

My question is how to use just one SQL query to count the number of poets by nation in poets and then update the corresponding count of that nation?

I tried:

UPDATE poets, nations SET nations.count = COUNT(poets.id) GROUP BY poets.nation HAVING poets.nation = nations.id

But it gives #1064 error. Also tried to combine WHERE clause somewhere but it still refuses to work.

Any idea?

Answer 1

Use a subquery:

UPDATE nations 
   SET count = (
       SELECT COUNT(id) 
         FROM poets 
        WHERE poets.nation = nations.id 
        GROUP BY id
       );

Answer 2

You just do not need GROUP BY, cf.:

UPDATE nations SET count = (
  SELECT COUNT(id) FROM poets 
    WHERE poets.nation = nations.id);

Or rather, with GROUP BY, the subquery will return NULL for nations that have no poets. Without GROUP BY, the subquery will return 0 in this case.