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

Question

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'

so the alias doesn't work. Use count(crime_id)?

juergen d
– juergen d

2013-06-05 09:13:00 +00:00
Commented Jun 5, 2013 at 9:13 — juergen d
– juergen d, Commented Jun 5, 2013 at 9:13
says Invalid use of group function

Rakib
– Rakib

2013-06-05 11:11:38 +00:00
Commented Jun 5, 2013 at 11:11 — Rakib
– Rakib, Commented Jun 5, 2013 at 11:11

Erik Schierboom · Accepted Answer · 2015-10-20 07:46:01Z

28

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

edited Oct 20, 2015 at 7:46

answered Jun 6, 2013 at 7:51

Erik Schierboom

16.7k10 gold badges67 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rakib Over a year ago

PERFECT :) .... However, in the SQL Fiddle, i think you forgot to add date , city AS the PRIMARY KEY... ALTER TABLE simple_crimecount` ADD PRIMARY KEY (date , city);` otherwise, it's not finding any DUPLICATE KEY at all... I am editing your answer with the update SQLfiddle.... thanks man :)

Erik Schierboom Over a year ago

You're welcome! I did forget to add the primary key, feel free to edit my post.

ypercubeᵀᴹ Over a year ago

You can also use ON DUPLICATE KEY UPDATE crimecount = VALUES(crimecount). See the Fiddle-2

Rakib Over a year ago

hey @ypercube, thanks for the update.... your update is worth being posted as a separate answer.... I think fiddle2 is the more recommended way to do such an update in case of a ON DUPLICATE KEY according to the MySQL developer documentation - dev.mysql.com/doc/refman/5.0/en/…

Danny Bullis Over a year ago

When using the first approach with setting a variable in the select statement (@determined_crimecount := count(crime_id)) I get the following warning in MySQL 8.0.16:

1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'

- for this reason, I opted with using the second approach by using VALUES(crimecount) in the update. This worked correctly and I safely avoided deprecation warnings. +1 for your helpful answer.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related