Delete duplicate rows

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mathewvp
Forum Commoner
Posts: 28
Joined: Wed Apr 23, 2003 10:28 am

Delete duplicate rows

Post 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?
Paddy
Forum Contributor
Posts: 244
Joined: Wed Jun 11, 2003 8:16 pm
Location: Hobart, Tas, Aussie
Contact:

Post 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. :)
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
Post Reply