MySQL - Delete rows from a table where columns duplicate [duplicate]

Question

Possible Duplicate:
how to delete duplicate rows from a table in mysql

I have a simple request which is becoming a nightmare.

I have a table 'MyTableA' with two columns ID(int) and Value(varchar(100))

The table has some values like so:

ID|Value
1|Apple
2|Apple
3|Apple

Wherever Value is a duplicate I want to keep the first ID row and delete the rest of the rows. So in the above example i want to delete rows with the ID 2 and 3.

I assumed this was simple but it might be because i'm using to SQL having clauses.

Thanks!

Answer 1

Try...

DELETE t FROM `dbtable` t
  JOIN `dbtable` t2 ON t.`Value` = t2.`Value`
WHERE t.`ID` > t2.`ID`