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

Question

我正在从 tableONE 查询并尝试将结果集插入到 tableTWO 中。这有时会导致 tableTWO 中出现重复键错误。所以我想使用来自 tableONE 结果集的新确定值 ON 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`];

它返回一个错误说明如下

Unknown column 'determined_crimecount' in 'field list'

Answer 1

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

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

---

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

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