报错时候的语句:
DELETE FROM YOUR_TABLE
where id in (
select MAX(id) from YOUR_TABLE where CONCAT(sddm,rq) in (
select CONCAT(sddm,rq) from YOUR_TABLE group by sddm,rq having count(1)>1
)
group by sddm
)
结果就遇到了 1093 - You can't specify target table 'YOUR_TABLE' for update in FROM clause
意思就是在 MySQL 中,您不能在 DELETE 语句的 FROM 子句中引用目标表,这是因为在 DELETE 语句中,目标表不能出现在 FROM 或 JOIN 子句中。您所尝试的查询违反了这个规则,因此收到了错误信息 You can't specify target table 'YOUR_TABLE' for update in FROM clause。
要解决这个问题,您可以再包装一层SELECT查询来确定要删除的记录,然后再执行 DELETE 语句。下面是一个修改后的示例,它将按照您的需求删除重复的记录。
下面是修复后的语句:
DELETE FROM YOUR_TABLE
where id in (
select id from (
select MAX(id) id from YOUR_TABLE where CONCAT(sddm,rq) in (
select CONCAT(sddm,rq3) from YOUR_TABLE group by sddm,rq having count(1)>1
)
group by sddm
) a
)
结果当然是删除成功了