How to delete the duplicate rows from a table in SQL Server
Below explained is one of ways to delete the duplicate rows
from the table in SQL Server. There might be lot more ways to accomplish this
task in a better way, but this is one of them.
Assume a table called “Room is populated with duplicate data”.
Snapshot is shown below.
Query to find the Duplicate data is:
Select * , row_number() over(partition by id order by id) as dup from room
Better Query to find the Duplicate data:
With temptable1
(C1,D)as
(
select id,row_number() Over (Partition by id order by id) as Dup from room
)
select * from temptable1 where D>1
Column “Dup” values greater than 1 are all duplicate rows and they are identified now and can be deleted easily.
How to delete the duplicate data:
With temptable1
(C1,D)as
(
select id,row_number() Over (Partition by id order by id) as Dup from room
)
Delete from temptable1 where D>1
Hope the above information helps
Thanks
