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
– Sharad Nikam, Commented Aug 21, 2017 at 9:39

M. Rezaeyan · Accepted Answer · 2017-08-21 09:54:22Z

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, 2017 at 9:54

answered Aug 21, 2017 at 9:45

M. Rezaeyan

4081 gold badge3 silver badges14 bronze badges

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

1 Comment

Jonathan Willcock Over a year ago

I think you need ";" before WITH

Vishwanath Dalvi · Accepted Answer · 2017-08-21 09:48:15Z

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;

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related