[RESOLVED] Deleting duplictates from Mysql table

[juniper747]

I have a table which lists friend relationships, with the following columns: requested_id, requesting_id, connection_type, request_date

In some cases (due to multiple friend requests between the same two members), I end up with duplicate entries in my table.

I am trying to come up with a mysql query which would DELETE, all of the duplicate rows BUT would leave only the most recent row.
I was thinking something along the lines of:

if($duplicatecounter > 1){
	mysql_query("DELETE friends WHERE (($id IN (requesting_id, requested_id)) AND ($sid IN (requesting_id, requested_id)))" LIMIT 1) or die("could not delete the rows");
}

But this does only appears to delete one row, but there may be more than only 1 duplicate. And it does not take into consideration the request_date... Any suggestions?

[BrightIdeas]

My recommendation would be to adapt the page that writes the friend requests into your database. Have that page check to see if there is a row with the same data already in your database, delete the old row and insert the new one. It eliminates the need to do a database cleanup afterwards (removing the duplicates with older dates)

[johanafm]

If duplicate values are not supposed to be allowed, then why allow them? What you want is a unique index on (requested_id, requesting_id), and in my opinion, there's your primary key for this table.