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

2022-01-10 00:00:00 duplicates mysql resultset insert-update

我正在从 tableONE 查询并尝试将结果集插入 tableTWO.这有时会导致 tableTWO 中出现重复键错误.所以我想ON DUPLICATE KEY UPDATE 使用tableONE 结果集中的新确定值，而不是使用ON DUPLICATE KEY UPDATE columnA = columnA 来忽略它.

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'

推荐答案

问题是在重复键子句中不能使用任何分组函数(如COUNT.但是，有一个简单的解决这个问题的方法.您只需将 COUNT(crime_id) 调用的结果分配给一个变量，您可以在重复的键子句中使用该变量.然后您的插入语句将看起来像这样:

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;

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

I have create an SQL Fiddle that shows you how it works: SQL-Fiddle

您也可以使用 UPDATE crimecount = VALUES(crimecount) 并且没有变量:

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);

请参阅 SQL-Fiddle-2

See the SQL-Fiddle-2

相关文章