目的: 一张表,表名report_roster 有employee_id人员id和 date 时间 字段
每个人 employee_id 在date 是 2017-12-01 有多条重复数据,现在只保留一条数据
一:首先是这么想的
delete from report_roster where id in (
SELECT max(id)
FROM report_roster
WHERE effDate = '2017-12-01'
GROUP BY employee_id
HAVING count(employee_id) > 1
) ;
发现在mysql中会报错
错误信息:[Err] 1093 - You can't specify target table 'report_roster' for update in FROM clause
于是查资料说是不能先select出同一表中的某些值,再update这个表(在同一语句中)
二:正确写法
delete from report_roster where id in (
SELECT * FROM (
SELECT max(id)
FROM report_roster
WHERE effDate = '2017-12-01'
GROUP BY employee_id
HAVING count(employee_id) > 1
) b
) ;
也就是说将select出的结果再通过中间表select一遍,这样就规避了错误。注意,这个问题只出现于mysql