从查询中插入结果集时，MySQL ON DUPLICATE KEY UPDATE

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.

我从tableONE查询并尝试将结果集插入tableTWO。这有时会导致tableTWO中出现重复键错误。因此，我希望使用来自tableONE结果集的NEW确定值打开DUPLICATE KEY UPDATE，而不是使用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'

1 个解决方案

#1

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:

问题是在重复键子句中你不能使用任何分组函数（例如COUNT。但是，有一个简单的方法可以解决这个问题。你只需将COUNT（crime_id）调用的结果赋给变量，你可以在重复键子句中使用。您的insert语句将如下所示：

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

我创建了一个SQL Fiddle，它向您展示它是如何工作的：SQL-Fiddle

You could also use UPDATE crimecount = VALUES(crimecount) and no variables:

您还可以使用UPDATE crimecount = VALUES（犯罪次数）而不使用变量：

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

请参阅SQL-Fiddle-2

#1

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;