Delete duplicated rows?

[karimali831]

Hi all,

 

I have mistakenly inserted duplicated rows and will take forever removing them manually so hoping someone can help me with the SQL to remove duplicated results.  You can see the image where the duplicated row begins and so on.

 

The rows userID, clanID and cupID shouldn't have a duplicate. I have fixed this in my script just need to get rid of the duplicates and obviously only having 1 one of the rows.

 

 

Thanks to anyone

[floridaflatlander]

You'r deleting it from phpmyadmin? I think this will do it if you put it in your sql of phpmyadmin fot that table

 

DELETE FROM table_name WHERE id != 16430; if 16430 is the record you want to keep

 

OR

 

DELETE FROM table_name WHERE id != 16430 And cupID = 45; for a little insurance and if you want to get rid of only the 45's

[smerny]

hope you didnt do that ^ you'll lose much more than you are hoping to

 

florida, he wants to remove any duplicate rows that cup, clan, and userIDs are all the same

[smerny]

i'd back it up first to be safe, but thats just me..

 

i'd try something like this

 

DELETE from table

USING table, table as t

WHERE (table.ID>t.ID)

AND (table.cupID=t.cupID)

AND (table.clanID=t.clanID)

AND (table.userID=t.userID)

[karimali831]

perfect exackly what I wanted, amazing thank you very much.