SQL删除重复数据

Question

[code="sql"]delete from biao where chongfuziduan in (select chongfuziduan from biao group by chongfuziduan having count(*)>1) and where qitatiaojian [/code]
为什么这样写，数据库报错？数据库是mysql，另求更好的删除重复数据方法

Answer 1

--这种方式不行，会抛出 You can't specify target table '×××' for update in FROM clause 因为mysql不支持在更新时 在子查询中出现同表

delete from a where chongfuziduan in (select a from biao group by chongfuziduan having count(*)>1)

1、有主键(id)情况下：
Delete a1
From biao a1
join biao a2 on a1.chongfuziduan =a2.chongfuziduan and a1.id> a2.id

没主键情况
1、没主键情况：
1.1、先建一个临时表(带自动生成主键)
1.2、将biao数据插入到临时表
1.3、然后进行【1】的操作
1.4、先删除biao数据，再把数据插入biao
1.5、删除临时表

2、
2.1、select distinct 数据 到临时表
2.2、删除biao数据 再插入临时表数据

[url]http://dadloveu.blog.51cto.com/715500/196309/[/url]