How to Delete Duplicate Date From Database

ADVERTISEMENT

---

Sometime we have a very challenging  task, Duplicate data in database store but we have to keep one data safe and need to be delete other data , duplicate data may store sometime because of restore database,
sometime migrating data from another database, some time our leakage so that we have to find and delete these duplicate from the database to purify the database

delete duplicate data from database means keep one record safe and delete other duplicate data
Delete Duplicate data from database and keep one record in Sql Server we need to have one primary key record


Syntax

WITH tblTemp as
(
SELECT ROW_NUMBER() Over(PARTITION BY [column name ]ORDER BY [column Name])
   As RowNumber,* FROM [TableName]
)
DELETE FROM tblTemp where RowNumber > 1

Here Don't change the other. change to column name by your desire or need delete data. and change the table name by your table
For eg

WITH tblTemp as
(
SELECT ROW_NUMBER() Over(PARTITION BY studentrollno ORDER BY studentrollno )
   As RowNumber,* FROM tblstudent
)
DELETE FROM tblTemp where RowNumber > 1