Page 1 of 1

Delete duplicate rows

Posted: Sat Oct 11, 2003 9:30 am
by mathewvp
How do I delete rows having duplicate data.Of course one row should be there remaining.I was asked to do this with a Delete query and using limit clause.But I am clueless.
Any help?

Posted: Sat Oct 11, 2003 6:45 pm
by Paddy
If you don't get an answer you could use a bandaid.

Get the data, delete the rows involved, reinsert the data.

Probably how I would do it, but I am slack. :)

Posted: Sat Oct 11, 2003 6:59 pm
by JAM
The following code will delete everything 5 times. Meaning, that if you have 10 rows of duplicates, 5 of them will go away. If you have 2 rows of duplicates, all of them will go away (perhaps even generate an error).

Code: Select all

delete from table_name where foo = 'bar' limit 5
Don't know if that helped, but if you only have 2 rows of duplicates for each 'thing' a limit setting of 1 would do the trick.