MySQL ON DUPLICATE KEY UPDATE while inserting a result set from a query
The problem is that in the duplicate key clauses you cannot use any grouping functions (such as COUNT. However, there is an easy way around this problem. You just assign the result of the COUNT(crime_id) call to a variable, which you can use in the duplicate key clauses. Your insert statement would then look like this:
INSERT INTO `simple_crimecount` (`date` , `city` , `crimecount`)( SELECT `date`, `city`, @determined_crimecount := count(`crime_id`) AS `determined_crimecount` FROM `big_log_of_crimes` GROUP BY `date`, `city`) ON DUPLICATE KEY UPDATE `crimecount` = @determined_crimecount;I have create an SQL Fiddle that shows you how it works: SQL-Fiddle
You could also use UPDATE crimecount = VALUES(crimecount) and no variables:
INSERT INTO `simple_crimecount` (`date` , `city` , `crimecount`)( SELECT `date`, `city`, count(`crime_id`) AS `determined_crimecount` FROM `big_log_of_crimes` GROUP BY `date`, `city`) ON DUPLICATE KEY UPDATE `crimecount` = VALUES(crimecount);See the SQL-Fiddle-2