Delete Duplicate Record in SQL Server

Let's create a table and name it NewTable.

Syntax

Create table NewTable

(

   Name varchar(30),

   Age int

)

Now, I am going to insert some demo record in my table using while loop.

declare @i int=0

while(@i<5)

begin

insert NewTable values('Record',15)

insert NewTable values('Record1',20)

insert NewTable values('Record2',25)

insert NewTable values('Record3',30)

set @i = @i+1

end

Now let's see the record count in table.

select count(*) from newTable

Output : 20

Now write a query for common table expression. After that write a query for delete.

with CTE AS(

select row_number() over(partition by name order by name) as RowNo, Name,Age from newTable

)

delete from CTE where RowNo > 1

I hope you enjoy this article.

Happy coding J