Delete duplicate entries keeping one entry of each if id column not available

Question

I have tried,

delete from Student where FirstName in
(
    select FirstName  from
    (
     select FirstName,
     row_number() over(partition by FirstName order by FirstName) as rn
     from Student
    ) Student
    WHERE rn > 1
);

but its deleting both duplicate records. please correct my query. thanks in advance.

No primary key and any id column. all columns are varchar type. — Sharad Nikam, Aug 21 '17 at 09:39
https://stackoverflow.com/questions/985384/delete-duplicate-records-from-a-sql-table-without-a-primary-key — Matt, Aug 21 '17 at 09:43
You are correctly identifying duplicates, and only one of each, in the inner `select`s. However, the `delete` statement couldn't care less how many duplicates you have, and will delete anything that has a match on `FirstName`. — Peter Abolins, Aug 21 '17 at 09:46
The simplest solution is to add a primary key to your table. That will save you problems in the future as well. — Peter Abolins, Aug 21 '17 at 09:56

M. Rezaeyan · Accepted Answer · 2017-08-21T09:54:22.907

2

You can use CTE

    WITH MyCTE 
    AS
    (
       SELECT [FirstName], ROW_NUMBER() OVER(PARTITION BY FirstName ORDER BY FirstName) RN
       FROM Student
    )
    DELETE FROM MyCTE WHERE RN > 1;
    SELECT * FROM Students;

edited Aug 21 '17 at 09:54

answered Aug 21 '17 at 09:45

M. Rezaeyan

388
2
14

I think you need ";" before WITH – Jonathan Willcock Aug 21 '17 at 09:52

score 0 · Answer 2 · answered Aug 21 '17 at 09:48

You're deleting based on FirstName hence it's deleting all the records from your table, try like this will do.

Live Demo

WITH StudentCte
As
(
SELECT FirstName, ROW_NUMBER() OVER(Partition by FirstName ORDER BY (SELECT NULL)) as RowNum
FROM Student
)

DELETE FROM StudentCte WHERE RowNum > 1;

SELECT * 
From Student;

Delete duplicate entries keeping one entry of each if id column not available

2 Answers2

Related