How to delete duplicate rows in SQL

Question

I have following data:

Id: 1       Name:   apple       ForeignKey: 10
Id: 2       Name:   apple       ForeignKey: 10
Id: 3       Name:   apple       ForeignKey: 15
Id: 4       Name:   peach       ForeignKey: 11
Id: 5       Name:   peach       ForeignKey: 12

Rows with same Name and ForeignKey are duplicates in my case. Now I want to remove all the duplicates except one instance from the table.

In other words; I want to remove all rows but one, where Name and ForeignKey are equal.

In case with upper data only the row with Id 2 OR Id 1 should be removed.

With

select count(Name), Name, ForeignKey group by Name, ForeignKey having count(Name)>1

I am able to find items where more than 1 row with same Name and Foreign key exist, but how to get the IDs of those rows? And how to get the IDs of those rows except the first/last occurrence of that row with same Name and ForeignKey?

Once you mopped the floor, remember to fix the leak - if you don't want duplicates in your database, apply a unique constraint against (Name,ForeignKey) (once you've removed the duplicates once) — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented May 13, 2014 at 14:18
@Damien_The_Unbeliever fixing the leak was the first I have done ;-) — maxyha
– maxyha, Commented May 13, 2014 at 14:25
possible duplicate of How do I delete duplicate data from SQL table — Kyle Hale
– Kyle Hale, Commented May 13, 2014 at 15:26

sgeddes · Accepted Answer · 2014-05-13 14:17:35Z

1

The answer if database specific, but here is how you can do it joining the table to itself:

delete t1
from yourtable t1
    join yourtable t2 on t1.id > t2.id
        and t1.name = t2.name 
        and t1.foreignkey = t2.foreignkey

SQL Fiddle Demo

answered May 13, 2014 at 14:17

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

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

1 Comment

maxyha Over a year ago

WOW, that was fast. Thank you again.

alroc · Accepted Answer · 2014-05-13 14:47:53Z

0

You can also do it with a CTE & window function, deleting the duplicate rows by counting the number of rows that are the same, and then deleting all but one.

SQL Fiddle demo

;WITH myvals
AS (
    SELECT [id]
        ,[name]
        ,[foreignkey]
        ,ROW_NUMBER() OVER (
            PARTITION BY [name]
            ,[foreignkey] ORDER BY [id]
                ,[name]
                ,[foreignkey]
            ) AS inst_count
    FROM yourtable
    )
DELETE
FROM myvals
WHERE inst_count > 1;

answered May 13, 2014 at 14:47

alroc

28.3k6 gold badges55 silver badges106 bronze badges

Comments

user3347005 · Accepted Answer · 2014-05-14 07:14:37Z

0

delete x from ( select *, rn=row_number() over (partition by name,foreignkey order by name) from yourtable ) x where rn > 1

answered May 14, 2014 at 7:14

user3347005

231 silver badge4 bronze badges

Collectives™ on Stack Overflow

How to delete duplicate rows in SQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related