Fork me on GitHub

【MySQL】解决You can't specify target table 'user_cut_record_0413' for update in FROM clause

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

原因
待更新/删除的数据集与查询的数据集撞车了,可以给后面的数据集加个别名,来解决撞车问题

报错语句

delete from user_cut_record_0413 where record_id IN ( select record_id from user_cut_record_0413 GROUP BY record_id HAVING COUNT(record_id) > 1 )

解析语句
delete from user_cut_record_0413 where record_id IN ( select record_id from user_cut_record_0413 GROUP BY record_id HAVING COUNT(record_id) > 1 )

解决方法

delete from user_cut_record_0413
where record_id IN (
	select record_id from (select * from user_cut_record_0413) t
	GROUP BY record_id
	HAVING COUNT(record_id) > 1
)

其实就是把后面的user_cut_record_0413 改成 (select * from user_cut_record_0413) t

posted @ 2018-06-04 09:58  _落雨  阅读(372)  评论(0编辑  收藏  举报