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