如何检查和删除所有重复项

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

但它不起作用

1 个解决方案

#1

You can do either with JOIN

你可以用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

这是SQLFiddle演示

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

这是SQLFiddle演示