How to delete duplicate records in SQL?

Question

This my original table:

Intended table:

I can't add a new column to table.

How do you decide which ones are the "good ones" and which ones are the "bad ones"? — The Impaler, May 20 '19 at 20:43
I think the main issue here originates in the absence of a primary key. — The Impaler, May 20 '19 at 20:47

M. Kanarkowski · Accepted Answer · 2019-05-20T20:50:32.673

5

You can delete duplicates using i.e. ROW_NUMBER():

with duplicates as
(
    select
        *
        ,ROW_NUMBER() OVER (PARTITION BY FirstName, LastName, age ORDER BY FirstName) AS number
    from yourTable
)
delete 
from duplicates
where number > 1

Each row where number is bigger than 1 is a duplicate.

edited May 20 '19 at 20:50

answered May 20 '19 at 20:46

M. Kanarkowski

2,155
8
14

3

careful, you didn't partition by `Age` as well which **is needed** to remove true *duplicates* – S3S May 20 '19 at 20:50

score 4 · Answer 2 · answered May 20 '19 at 20:48

You can use a common table expression to delete all but the first record in a set of duplicates:

with cte as (
    select *, row_number()
     over (partition by FirstName, LastName, Age order by FirstName, LastName, Age) as row_number
    from tbl
)
delete from cte where row_number <> 1

score 1 · Answer 3 · answered May 20 '19 at 20:51

You need some way of identifying the rows in the absence of a PK.

The only way I can think of is to:

Retrieve the "Row IDs" of the rows,
Then pick one as the "good one" for every non-unique row,
And finally delete all the other ones.

I'm not positive about this solution, though. I think this answer can help to produce distinctive row ids. I hope it helps.

How to delete duplicate records in SQL?

3 Answers3