You can't specify target table for update in FROM clause

发表于 更新于 分类于 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
今天通过命令行想删除一些重复的数据结果执行命令的时候报错了：
执行的命令：
DELETE FROM cms_mail where m_create_time < 1585801800 
and  m_mail_uid in (select m_mail_uid from cms_mail 
group by m_to_email, m_mail_uid having count(m_mail_uid)> 1)

结果：
You can't specify target table for update in FROM clause

解决方案：
DELETE FROM cms_mail where m_create_time < 1585801800 
and  m_mail_uid in (
select m_mail_uid from ( select m_mail_uid from cms_mail group by m_to_email, m_mail_uid having  count(m_mail_uid)> 1) as a
)

原因是mysql不支持你修改的值包含子查询的内容。把子查询语句变成一个临时表来处理就ok了