如何只删除重复的行？

How I can create an SQL command to delete all rows from table where I have two or more specific columns with the same value and still I don't lose that row, only the duplicates?

我如何创建一个SQL命令来删除表中的所有行,其中我有两个或更多具有相同值的特定列,但我仍然没有丢失该行,只有重复项?

For example:

Id      value1     value2
1          71            5
2          8             8
3          8             8
4          8             8
5          23           26

Id2, Id3 and Id4 have same value1 and value2.

Id2,Id3和Id4具有相同的value1和value2。

I need to delete all duplicate rows like (Id3 and Id4) or (Id2 and Id4) or (Id2 and Id3)

我需要删除所有重复的行,如(Id3和Id4)或(Id2和Id4)或(Id2和Id3)

3 个解决方案

#1

delete t
from table1 t
inner join table1 t2
on t.id>t2.id and t.value1=t2.value1 and t.value2=t2.value2

#2

Since MySQL allows ungrouped fields in queries:

由于MySQL允许在查询中使用未分组的字段:

CREATE TEMPORARY TABLE ids AS
  (SELECT id
    FROM your_table
    GROUP BY value1, value2);

DELETE FROM your_table
  WHERE id NOT IN (SELECT id FROM ids);

#3

What you can do is copy the distinct records into a new table by:

您可以做的是通过以下方式将不同记录复制到新表中:

select distinct * into NewTable from MyTable

#1

delete t
from table1 t
inner join table1 t2
on t.id>t2.id and t.value1=t2.value1 and t.value2=t2.value2

#2