且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

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

更新时间:2023-02-26 10:42:12

问题是,在重复的关键字段中,您不能使用任何分组函数(例如 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