Delete duplicate records

[KingSlick]

I have a table that contains over 100k records. The problem that I have is there are about 5k records that are duplicates. All of these records are duplicated twice and the only field that is the same is the primary key. Is there any query that I could use that will only delete one of each of the duplicated records.

Thanks in advance.

-SM

 

[audiopro]

the only field that is the same is the primary key

So they are duplicate primary keys not duplicate records.
How will you know which one of the duplicated records is the correct one to delete?


Keith

http://www.studiosoft.co.uk

 

[KingSlick]

Ooops. Sorry. What I meant was the primary key is the only field that is different.

Either of the duplicated records can be deleted as long as there is only 1 remaining.

Thanks

 

[audiopro]

Do you know how the duplicates were created?
Are they consecutive or scattered?

Keith

http://www.studiosoft.co.uk

 

[KingSlick]

I have an update script that adds them to the table. There was a problem with the script and had to be restarted. This caused the duplicates to be added again. So they are semi-scattered. An example is below. As you can see, the id is the primary key.

____________
|id | code |
------------
|123 | 987 |
|124 | 988 |
|125 | 989 |
|126 | 990 |
|127 | 987 |
|128 | 988 |
|129 | 989 |
|130 | 990 |

 

[audiopro]

When the table is ok, are all the values in code guaranteed to be diferent?
What script language are you using?

Keith

http://www.studiosoft.co.uk

 

[KingSlick]

Yes all the records will have a unique code field. I have corrected the problem. I just deleted everything with an id greater than that of where the problem started. But thanks for all of the help anyways.

SM

 

[audiopro]

Glad your sorted

Keith

http://www.studiosoft.co.uk

 

[guelphdad]

SM here's an article that can help you if you run into a future problem with removing duplicates.

 

[Kozusnik]

You may also want to make that field UNIQUE. You could always add an ON DUPLICATE KEY UPDATE... to your INSERT statement(s).

Mark