3

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!

Community
  • 1
  • 1
  • @Asad really it's not I kept the example simple the real issue is that it's not a 3 row table but more along the line sof a 50k row table with about 100 lines –  Nov 17 '12 at 19:19
  • 2
    The same solution could be applied though, couldn't it? Did you try the approach suggested? – Asad Saeeduddin Nov 17 '12 at 19:28
  • I'm not sure how the number of rows in the table even remotely relates to whether or not this is a duplicate question. You are asking very simply: "How do I delete duplicated data in a MySQL table" which is very clearly answered by Asad's linked post. – StudyOfCrying Nov 17 '12 at 19:33
  • 1
    As outlined in Asad's linked duplicate question, one approach for this would be to create a temp table with the data you'd like to delete, then inner join that table back to delete the targeted rows. Other approaches exist as well. This is a problem you'll want to understand for a lot of reasons, not the least of which being that its an extremely common interview question. – StudyOfCrying Nov 17 '12 at 19:35

2 Answers2

5

Try...

DELETE t FROM `dbtable` t
  JOIN `dbtable` t2 ON t.`Value` = t2.`Value`
WHERE t.`ID` > t2.`ID`
rajukoyilandy
  • 5,341
  • 2
  • 20
  • 31
0

you can possible delete it by joining the table with a subquery (the subquery gets the minimum ID per value)

DELETE a FROM tableName a
        LEFT JOIN
        (
          SELECT Value, MIN(ID) minID
          FROM tableName
          GROUP BY Value
        ) b ON a.ID = b.minID
WHERE  b.minID IS NULL
John Woo
  • 258,903
  • 69
  • 498
  • 492