MySQL 用其他字段的 COUNT 更新一列是 SQL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6135226/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update a column with a COUNT of other fields is SQL?
提问by Ali
Hi guys I have the following tables set up:
大家好，我设置了以下表格：
Articles:
ID | TITLE | CONTENT | USER | NUM_COMMENTS
COMMENTS
ID | ARTICLE_ID | TEXT
I need a sql statement which updates the NUM_Comments field of the articles table with teh count of the comments made against the article like:
我需要一个 sql 语句，它更新文章表的 NUM_Comments 字段，其中包含对文章发表的评论数，例如：
update articles a, comments f
set a.num_comments = COUNT(f.`id`)
where f.article_id = a.id
The sql above doesn't work and I get an Invalid Use fo Group function error. I'm using MySQL Here.
上面的 sql 不起作用，我收到一个 Invalid Use fo Group 函数错误。我在这里使用 MySQL。
回答by No'am Newman
You can't have a join in an update statement. It should be
您不能在更新语句中加入。它应该是
update articles
set num_comments =
(select count (*) from comments
where comments.article_id = articles.id)
This will update the entire articles table, which may not be what you want. If you intend to update only one article then add a 'where' clause after the subquery.
这将更新整个文章表，这可能不是您想要的。如果您打算只更新一篇文章，则在子查询后添加一个“where”子句。
回答by Deniss Kozlovs
This should work.
这应该有效。
UPDATE articles a SET num_comments =
(SELECT COUNT(*) FROM comments c WHERE c.article_id = a.id)
But i would rather update only one record when comment has been posted:
但我宁愿在发表评论时只更新一条记录：
UPDATE articles a SET num_comments =
(SELECT COUNT(*) FROM comments c WHERE c.article_id = 100) WHERE a.id = 100
回答by ctoepfer
To update based on a column count alone, you could do something like:
要仅根据列数进行更新，您可以执行以下操作：
update articles,
(select count (*)
from comments
where comments.article_id = articles.id) as newtotals
set articles.num_comments = newtotals.count;
or ... if you had a situation that required rolling counts:
或者...如果您遇到需要滚动计数的情况：
update articles,
(select (count (*)) + (articles.num_comments) as count
from comments
join articles on
comments.article_id = articles.id
group by articles.id) as newtotals
set articles.num_comments = newtotals.count;
回答by rapttor
count (*) might have some problems, especially with blank space between count and (*) ...
count (*) 可能有一些问题，尤其是 count 和 (*) 之间的空格...
so working sql on sqlite, pgsql would be:
所以在 sqlite 上运行 sql，pgsql 将是：
update articles
set num_comments =
(select count(id) from comments
where comments.article_id = articles.id)
回答by Moa'ath
you cant do it in a generic inner join way. but you can do it in another way by:
你不能用通用的内部连接方式来做。但您可以通过以下方式以另一种方式做到这一点：
1- Select all the ids from the articles table
1- 从文章表中选择所有 id
2- iterate them and execute the following command
2-迭代它们并执行以下命令
update articles set NUM_COMMENTS = (select count(id) from comments where id = $id) where id = $id
更新文章集 NUM_COMMENTS = (select count(id) from comments where id = $id) where id = $id
to enhance it more, in the 1st select dont select all the values especially when that table is too large, you need to iterate the articles and get 1000 records per iteration. This way u will maintain a healthy DB threads from your DB pool and you also save bandwidth.
为了进一步增强它，在第一个选择中不要选择所有值，尤其是当该表太大时，您需要迭代文章并每次迭代获得 1000 条记录。通过这种方式，您将从您的数据库池中维护一个健康的数据库线程，并且您还可以节省带宽。
