In this article we learn that how to delete duplicate row in sql.
Let’s create a table and name it NewTable.
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.