Sharad Nikam Sharad Nikam - 1 month ago 6
SQL Question

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

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.

Answer Source

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;