AM发现难以删除重复记录。
想删除记录
这是我的查询
delete from ldataphd.srvalt3 where a3_loan in (select a3_loan from
ldataphd.srvalt3 group by a3_loan having(*) >1)
上方查询删除所有记录。
表:
a3_loan a3_seq
1744 1
1744 32
1844 1
1844 35
预期输出
a3_loan a3_seq
1744 1
1844 1
nb-您需要先在表的测试副本上进行此操作!
当我这样做时,我发现除非我还包括N1.ID<> n2.ID,它删除了表中的每一行。
1(如果要保持最低ID值的行:
DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name
2(如果要保持具有最高ID值的行:
DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name
我在mySQL 5.1
中使用了此方法不确定其他版本。
更新:由于人们谷歌搜索删除重复的人最终在这里出现尽管OP的问题是关于删除的,但请建议使用插入和不同的速度要快得多。对于具有800万行的数据库,以下查询花费了13分钟,而使用删除则花费了2个多小时,但没有完成。
尝试此代码....
DELETE FROM MyTable
LEFT OUTER JOIN (
SELECT MIN(RowId) as RowId, Col1, Col2, Col3
FROM MyTable
GROUP BY Col1, Col2, Col3
) as KeepRows ON
MyTable.RowId = KeepRows.RowId
WHERE
KeepRows.RowId IS NULL
DELETE n1 FROM table n1, table n2 WHERE n1.a3_seq > n2.a3_seq AND n1.a3_loan = n2.a3_loan
这可能对您有帮助..!
select distinct * into #tmp From EmpDup
delete from EmpDup
insert into EmpDup
select * from #tmp drop table #tmp
select * from EmpDup
请参阅以下内容:http://www.besttechtools.com/articles/article/sql-query-to-delete-duplate-rows-rows