self join

[greg252]

any easy code to find duplicate records and delete 1 of them?

Thanks,
Greg

[Lars_Berg]

When you say delete 1 of them, I assume that you actually mean all but 1.

Is there some unique column in the table? Or are all columns identical in some records? If there is a counter (for instance) the operation can be done in one delete statement

delete from t where pk not in (select min(pk) from t group by c1,c2,c3)

(The above code does not (of course) work in Mysql.)

Otherwise it can be done in a few steps

0.) Backup your data
1.) Create an identical table (t2)
2.) insert into t2 select distinct from t
3.) Check that t2 contains the desired result
4.) delete from t
5.) insert into t select from t2
6.) drop table t2

[tomhath]

If every column is identical (no unique id) then create a temporary table and populate it with this. Then truncate the real table and repopolate from the temp.

insert into tempTable
select distinct * from theTable

If you have a unique id column you can find the dupes with something like:

select col1, col2, ... from theTable
group by col1, col2, ...
having count(*) > 1

Make sure you back up you database first in case something goes wrong.