mysql重复记录删除其中一条的办法

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/u013593306/article/details/50557619

场景:出现了两条一模一样的记录,除了例如createime和id,其他都相同,现需要删除例如重复记录中id小的,保留一条且id较大的。


SQL:

select * from 表名 where 值A in (select 值A from 表名 where 值B = 'B值' group by 值A having count(值A) > 1) and id not in (select min(id) from 表名 where 值B = 'B值' group by 值A having count(值A) > 1) and 值B = 'B值';

查出来然后删掉即可。

若采用如下方式会报错,解决方式待更新:

delete from 表名 where 值A in (select 值A from 表名 where 值B = 'B值' group by 值A having count(值A) > 1) and id not in (select min(id) from 表名 where 值B = 'B值' group by 值A having count(值A) > 1) and 值B = 'B值';

报错:

[Err] 1093 - You can't specify target table '表名' for update in FROM clause


展开阅读全文

没有更多推荐了,返回首页