MySQL: Count the Number of Records and Update the Stats Field in Another Table

• inShare

 Actually, here’s a much better idea: use a sub query. For example, you have 2 tables,

• 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.

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:

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