[Resolved] Removing database duplicates

[Arc]

Hello. I have a table in a MySQL DB that has somehow gotten duplicate rows of every single row. So my question is, what would the SQL be to remove all the duplicate rows?

Heres an example of the layout.

Address | Read | Sent | Active | etc...

Would I Need to do a SELECT and use DISTINCT Address to get 1 copy of each duplicate, then DELETE FROM the table and then INSERT the SELECT result?

Or is there a simpler way to just use DELETE FROM tblName WHERE blah....

Thanks alot!😃

[dalecosp]

Originally posted by Arc
Would I Need to do a SELECT and use DISTINCT Address to get 1 copy of each duplicate, then DELETE FROM the table and then INSERT the SELECT result?

Sounds like a fairly safe option. And I'd dump the DB first, as well, if it had anything in it I didn't wanna have to recreate in case my script blew up....

[drawmack]

This depends on how the duplicates have been created.

First do this

ALTER TABLE tabe ADD COLUMN TEMP_NUM BIGINT NOT NULL auto_increment;
ALTER TABLE table ADD UNIQUE TEMP_NUM (TEMP_NUM);

then
If the table looks like this
Record
Duplicate
Record
Duplicate
then I would do this

DELETE FROM table WHERE (TEMP_NUM%2)=0;

if the table looks like this
record
record
record
.
.
.
duplicate
duplicate
duplicate
then I would do this

DELETE FROM table WHERE TEMP_NUM > ((SELECT MAX TEMP_NUM FROM table)/2)

then clean up with this

ALTER TABLE table DROP COLUMN TEMP_NUM

[Arc]

Yah I ended just using the DIstinct option. I still ended with quite a few duplicates though as 1 of the fields was different on some of them. So I had to delete those by hand.

Thanks!😃