MAX() 与重复密钥更新 [英] MAX() vs ON DUPLICATE KEY UPDATE

问题描述

   INSERT INTO counters (REQUEST_ID, MODIFIED_DATE_WFM) 
SELECT 
si_id, MAX(modified_date) AS field 
FROM tt 
group by si_id
  ON DUPLICATE KEY UPDATE
MODIFIED_DATE_WFM = VALUES(modified_date)

错误 SQL (1054):字段列表"中的未知列修改日期"

ERROR SQL (1054): Unknown column 'modified_date' in 'field list'

我尝试使用子查询:

select * from (SELECT 
    si_id, MAX(modified_date) AS field 
    FROM tt 
    group by si_id) sub1

和别名:

  ON DUPLICATE KEY UPDATE
MODIFIED_DATE_WFM = VALUES(field)

但是选择:

SELECT 
    si_id, MAX(modified_date) AS field 
    FROM tt 
    group by si_id

工作

测试:适合表:

 CREATE TABLE `counters` (
        `REQUEST_ID` BIGINT(20) NULL DEFAULT NULL,
        `MODIFIED_DATE_WFM` INT(11) NULL DEFAULT NULL,
        UNIQUE INDEX `REQUEST_ID` (`REQUEST_ID`)
    )
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB
    ;

第二个表:

CREATE TABLE `tt` (
    `si_id` BIGINT(20) NOT NULL,
    `modified_date` INT(11) NOT NULL,
    INDEX `si_id` (`si_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

数据:

INSERT INTO `tt` VALUES (2574604, 1486086672);
INSERT INTO `tt` VALUES (2574604, 1487116221);
INSERT INTO `tt` VALUES (2574604, 1487971423);
INSERT INTO `tt` VALUES (2574604, 1488136028);
INSERT INTO `tt` VALUES (2574604, 1488136324);
INSERT INTO `tt` VALUES (2574604, 1488524248);
INSERT INTO `tt` VALUES (2574604, 1488728989);
INSERT INTO `tt` VALUES (2834403, 1488782168);

服务器版本:5.7.16 MySQL 社区服务器 (GPL)

Server version: 5.7.16 MySQL Community Server (GPL)

推荐答案

您的更新更新是错误的.试试这个:

Your update in on update is wrong. Try it with this:

INSERT INTO counters (REQUEST_ID, MODIFIED_DATE_WFM) 
SELECT 
si_id, MAX(modified_date) AS `field` 
FROM tt 
group by si_id
  ON DUPLICATE KEY UPDATE
MODIFIED_DATE_WFM = VALUES(MODIFIED_DATE_WFM);

在这里查看:http://rextester.com/MCR40199

这篇关于MAX() 与重复密钥更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！