MySQL INSERT INTO/ON DUPLICATE KEY 与 SELECT 语句问题 [英] MySQL INSERT INTO / ON DUPLICATE KEY with SELECT statement issue

问题描述

你好 - 我是 MySQL 菜鸟.我有一个包含各种商业列表的表格，我正在尝试填充第二个名为城市的表格，其中包含唯一的城市名称以及每个城市的列表数量.我可以执行一个 SELECT 语句来获取这些数据，如下所示:

Howdy - I'm a MySQL Noob. I have a table of various business listings and I am trying to populate a second table called cities that contains unique city names along with a count of how many listings per city. I'm able to do a SELECT statement that gets me this data fine like so:

SELECT city,state,sum(count)
FROM (
SELECT city,state, 1 AS count FROM listings
) results
GROUP BY city
ORDER BY sum(count) DESC,city;

但是，现在我想更新表格，但我似乎无法获得正确的语句来工作.这是我拥有的最新版本，但我目前收到无效使用组功能"错误.

However, now I want to update the table, but I can't seem to get a proper statement to work. This is the latest that I have, but I'm currently getting a "Invalid use of group function" error.

INSERT INTO cities(city,state,size)
SELECT city,state,sum(count)
FROM (
SELECT city,state, 1 AS count FROM listings
) results
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), size=sum(count); 

感谢任何帮助！

推荐答案

这样简单的东西能行吗?

Would something as simple as this work?

insert into cities (city, state, size)
select city, state, count(*) as size from listings
group by city, state

group by 应该确保没有重复项，这样就不需要 on duplicate key.你正在做的 sum() + subquery 看起来你只是想做一个 count(*).

group by should ensure that there are no duplicates so that there is no need for on duplicate key. The sum() + subquery thing you were doing looks like you were just trying to do a count(*).

您遇到的具体错误是由于 size=sum(count).在批量插入中，正确的方法是 size=values(size)，请参阅 values().

The specific error you were getting was because of the size=sum(count). In a batch insert the correct way to do this would be size=values(size), see the docs on values().

如果它为每个城市添加另一个条目，那么城市上没有唯一索引，并且重复键无论如何都不会做任何事情.

If it's adding another entry for each city then there isn't a unique index on city and on duplicate key won't do anything anyway.

如果您在 (city, state) 上添加唯一索引，那么您可以将 on duplicate key update size=values(size) 添加到上述查询中，它将更新每条记录.

if you add a unique index on (city, state) then you can add on duplicate key update size=values(size) to the above query and it will update each record in place.

这篇关于MySQL INSERT INTO/ON DUPLICATE KEY 与 SELECT 语句问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！