从查询中插入结果集时，MySQL ON DUPLICATE KEY UPDATE [英] MySQL ON DUPLICATE KEY UPDATE while inserting a result set from a query

问题描述

我正在从tableONE查询并尝试将结果集插入到tableTWO中。这可能会导致tableTWO中的重复键错误。所以我想要使用来自tableONE结果集的新确定的值，而不是忽略它与 ON DUPLICATE KEY UPDATE columnA = columnA c> ON DUPLICATE KEY UPDATE / code>。

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`];

它返回错误，说出以下

Unknown column 'determined_crimecount' in 'field list'

推荐答案

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

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;

我h ave创建一个SQL小提琴，向您显示如何运作： 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

这篇关于从查询中插入结果集时，MySQL ON DUPLICATE KEY UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！