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

问题描述

你好-我是MySQL Noob.我有一个包含各种业务列表的表，并且我试图填充第二个表，称为城市"，该表包含唯一的城市名称以及每个城市有多少个列表.我能够执行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()+子查询操作似乎只是在尝试执行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)，请参见

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.

如果在(城市，州)上添加唯一索引，则可以将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屋！