MySQL:从一个表中计数记录，然后更新另一个表 [英] MySQL: Count records from one table and then update another

问题描述

有2个表/实体，非常简单的场景.

Got 2 tables / entities, very straightforward scenario.

表诗人-列:身份证，诗人，国家

Table poets - Columns: id, poet, nation

表国家/地区-列:id，国家/地区，计数

Table nations - Columns: id, nation, count

基本上，民族到诗人自然具有一对多的映射.例如，有来自60个国家的1000名诗人. poets 中的每位诗人都通过国家"字段分配给一个国家，其中包含民族中一个国家的ID.

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.

我的问题是如何仅使用一个SQL查询来按国家/地区计算诗人中的诗人人数，然后更新该国家/地区的相应人数?

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?

我尝试过:

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

但是它给出了#1064错误.还尝试在某处组合WHERE子句，但它仍然无法正常工作.

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

有什么主意吗?

推荐答案

使用子查询:

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

这篇关于MySQL:从一个表中计数记录，然后更新另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！