How to check and delete all the duplicates

Question

Example of table

 id  name       value 1    value 2
 1   disco      numeber 1  0
 2   disco      numeber 2  0
 3   cheese     numeber 3  0
 4   something  numeber 4  0
 5   something  numeber 5  0

I want a code to delete all the rows which have duplicate names and leave me only with unique values.

The result should be

 id  name       value 1    value 2
 1   disco      numeber 1  0
 3   cheese     numeber 3  0
 4   something  numeber 4  0

I have this code

delete from your_table 
 where id not in 
select * from 
( select min(id) 
   from your_table 
  group by user_id, badge_id ) x )

but it doesn't work

Hi. Welcome to Stack Overflow! Stack Overflow is not really a platform where you can fill in your requirements and expect people to write your code for you using these requirements. For more information please consult the [help center](http://stackoverflow.com/help). To salvage your question please show us what you have tried, what the problem is with your solution and what part in specific you are stuck on. — PeeHaa, Nov 10 '13 at 22:14
http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-mysql-table — Dan Walker, Nov 10 '13 at 22:15
delete from your_table where id not in select * from ( select min(id) from your_table group by user_id, badge_id ) x ) i have this code but is doesent work — Alex Goaga, Nov 10 '13 at 22:16

peterm · Accepted Answer · 2013-11-10T23:37:34.943

0

You can do either with JOIN

DELETE t FROM table1 t JOIN
(
  SELECT name, MIN(id) id
    FROM table1
   GROUP BY name
) q 
    ON t.name = q.name 
   AND t.id <> q.id;

Here is SQLFiddle demo

or with a subquery

DELETE FROM table1
 WHERE id NOT IN
(
  SELECT id
    FROM
  (
    SELECT MIN(id) id
      FROM table1
     GROUP BY name
  ) q
);

Here is SQLFiddle demo

edited Nov 10 '13 at 23:37

answered Nov 10 '13 at 23:32

peterm

91,357
15
148
157

How to check and delete all the duplicates

1 Answers1