CODEWITHSUNDEEP
mysqlsqlduplicates
konjf1
konjf1

Reputation: 21

How to delete only duplicate rows?

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)

Upvotes: 2

Views: 462

Answers (3)

Naveen Kumar
Naveen Kumar

Reputation: 4601

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

select distinct * into NewTable from MyTable

Upvotes: 0

Nigel Tufnel
Nigel Tufnel

Reputation: 11534

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

Upvotes: 0

Vikram
Vikram

Reputation: 8333

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

Upvotes: 2

Related Questions