Remove duplicate rows from table
By mistakenly , non-primary key table data inserted into SQL Table. what you will do ?
We dun have any option without deleting it. If we not used Savepoint , then how to rollback inserted data ?
Remove table , --- > No
Remove rows one by one --> No
Use CTE ( Common Table Expression)
Query :
I have inserted following data two times
insert into [Test].[dbo].[Student] values(1,'nihar','pune')
insert into [Test].[dbo].[Student] values(2,'rohan','nashik')
insert into [Test].[dbo].[Student] values(3,'vishal','pabal')
Remove duplicate rows :
Query :
WITH OrderedResults AS
(
SELECT [studno]
, ROW_NUMBER() OVER (PARTITION BY [studno] ORDER BY [studno]) AS RowNumber
FROM [Test].[dbo].[Student]
)delete
from OrderedResults
WHERE RowNumber!= 1 Select whole query and execute it, Duplicate entries will be removed from table.Think over it.
We dun have any option without deleting it. If we not used Savepoint , then how to rollback inserted data ?
Remove table , --- > No
Remove rows one by one --> No
Use CTE ( Common Table Expression)
Query :
I have inserted following data two times
insert into [Test].[dbo].[Student] values(1,'nihar','pune')
insert into [Test].[dbo].[Student] values(2,'rohan','nashik')
insert into [Test].[dbo].[Student] values(3,'vishal','pabal')
Remove duplicate rows :
Query :
WITH OrderedResults AS
(
SELECT [studno]
, ROW_NUMBER() OVER (PARTITION BY [studno] ORDER BY [studno]) AS RowNumber
FROM [Test].[dbo].[Student]
)delete
from OrderedResults
WHERE RowNumber!= 1 Select whole query and execute it, Duplicate entries will be removed from table.Think over it.
Comments
Post a Comment