How can I delete duplicate MySQL entries?

[Dimoop]

Exactly as the subject says: How can I delete duplicate entries in MySQL?

Thanks

[zone16]

try
delete from mytable where myfield=1 limit 1

good luck,

zone16

[Dimoop]

where myfield=1? Can you please explain? Thanks!

[zone16]

I was assuming that your trying to delete one of the duplicate lines you have so:

if you have duplicate lines in table mytable:

field1 field2 field3
22 blabla ....
22 blabla ....

the sql would be

delete from mytable where field1=22 limit 1

hope that's what u're talking about..

layter,

zone16

[Dimoop]

Thing is I don't know how many duplicates I have :/

[zone16]

humm, next time add an unique index :-)

I'm not quite sure what the fastest way is in this case, but what you can do is insert dinstinct data from your duplicate table in a temp table, delete all entries in in your table and insert from your temp table into your table...

sounds bad i know, but it'll work(I'm sure there's an easier way..anyone out there?).

1. create temp table(let's say tmp_table) with the same structure as original table(dup_table)
2. insert tmp_table select distinct field1,field2 from dup_table
   (or insert tmp_table select field1,field2 from dup_table group by field1,field2)
   3 delete from dup_table
   4 insert dup_table select * from tmp_table

make sure you backup your data first!!!

tell me if that works for you,

laters,

zone16

[mattr]

insert into table2 ( select distinct * from yourtable )

drop table yourtable

rename table2 to yourtable

That's pseudocode, but the jist of it is select distinct into a new table, drop the old one, then rename the new table to the old name.

And whatever you do, CREATE A PRIMARY KEY ON THE TABLE.

[trooper]

Another viable method is to do the following

Load all the data into arrays then do array_unique on the array - that will remove any duplicates that exist. I mean duplicates in the values of the array

Then simply load the data back into the db

HTH

GM