MySQL ON DUPLICATE KEY UPDATE while inserting a result set from a query

duplicates, insert-update, mysql, resultset

Solution

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

Problem

I am querying from tableONE and trying to insert the result set into tableTWO. This can cause a duplicate key error in tableTWO at times. So i want to `ON DUPLICATE KEY UPDATE` with the NEW determined value from the tableONE result set instead of ignoring it with `ON DUPLICATE KEY UPDATE columnA = columnA`. ``` 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` = `determined_crimecount`; # instead of [ON DUPLICATE KEY UPDATE `crimecount` = `crimecount`]; ``` It returns an error saying the following ``` Unknown column 'determined_crimecount' in 'field list' ```

Original source