本文介绍了MAX() 与重复密钥更新的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
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() 与重复密钥更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!