MySql 删除重复数据的方法

1、网上介绍的方法如下：

DELETE FROM p2p_order
WHERE user_id IN (SELECT user_id FROM p2p_order GROUP BY user_id HAVING COUNT(user_id) > 1)
AND id NOT IN (SELECT  MAX(id) FROM p2p_order GROUP BY user_id HAVING COUNT(user_id) > 1);

 执行上述SQL,报错：

<e>查询：delete from p2p_order where user_id in (SELECT user_id FROM p2p_order GROUP BY user_id HAVING COUNT(user_id) > 1) and id not in ...

错误代码： 1093
You can't specify target table 'p2p_order' for update in FROM clause

 上述错误的意思是：不能在delete中执行一个查询操作。优化方案如下：

CREATE TABLE tmp AS SELECT  MAX(id) t_id FROM p2p_order GROUP BY user_id HAVING COUNT(user_id) > 1;

DELETE FROM p2p_order  WHERE id NOT IN (SELECT  t.t_id FROM tmp AS t);

DROP TABLE tmp;

 执行delete时，请检查一下tmp中是否有数据，防止误删除。

评论

1 楼 bo_hai 2019-03-15  

我来评论一下。