How to delete only duplicate rows?

Question

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?

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.

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

Answer 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

Answer 2

Since MySQL allows ungrouped fields in queries:

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);

Answer 3

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

select distinct * into NewTable from MyTable