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)

I thought distinct worked on a column level. Not a field level. As the Ids differ, Distinct wont work? — IAmGroot
– IAmGroot, Commented Feb 29, 2012 at 11:59

Dalen · Accepted Answer · 2012-02-29 11:45:04Z

2

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

edited Feb 29, 2012 at 11:45

Dalen

9,0264 gold badges49 silver badges53 bronze badges

answered Feb 29, 2012 at 11:42

Vikram

8,32335 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nigel Tufnel · Accepted Answer · 2012-02-29 11:52:33Z

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

Naveen Kumar · Accepted Answer · 2012-02-29 12:33:15Z

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

select distinct * into NewTable from MyTable

Collectives™ on Stack Overflow

How to delete only duplicate rows?

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related