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

---

您也可以使用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