mysql inser into on,MySQL INSERT INTO / ON DUPLICATE KEY与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);

Any help is appreciated!

解决方案

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 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(*).

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().

EDIT:

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.

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.