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
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.
Try this, though I wish I had something to test it on before unleashing it on you.
insert into cities (city, state, size) select city, state, count(*) AS size from listings group by city, state