mysql 删除重复的数据

首先我们看下这张表,title2,和title3 是重复的,我们的目标就是要找到他们然后删除他们。

找到他们的方法是:

首先是找到不是他们的id

select min(id),title,body,createDate from article group by title

这里使用了min和group by相结合,可以试试用max, 这样就是把id 2和3的作为要删除的对象了。假设我们这边的需求是保留旧的文章,就用min

那么是他们的id就再用一个子查询即可

select id, title,body, createDate from article where id not in (select min(id) from article group by title)

要删除他们,delete 加子查询即可

delete from article where id in (select id from article where  id not in (select min(id) from article group by title))

 

#1093 – Table ‘article’ is specified twice, both as a target for ‘DELETE’ and as a separate source for data, 报错了。改成临时表即可。

delete from article where id in (select id from (select id from article where  id not in (select min(id) from article group by title)) as temple)

http://www.waitingfy.com/archives/3530

3530

Leave a Reply

Name and Email Address are required fields.
Your email will not be published or shared with third parties.