Share This Article:

Subscribe to Kavoir:

I wrote this before in 2 separate posts. 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);

Share This Article:

Subscribe to Kavoir:

You should also read:

• MySQL: Add Statistics Column for the Number Count of Records from Another Table
• SQL: CREATE TABLE … SELECT … to generate new table from existing tables
• MySQL: Deleting Duplicate Rows or Records | Selecting Unique Rows into A New Table
• MySQL: Update Multiple Rows or Records with One Single Query
• SQL: INSERT INTO … SELECT … to generate or combine records from existing ones and insert into an existing table