This article explains “How to delete duplicate records from MS-SQL Table?”. I am giving you a following presumptive situation:
Table Name : Personal_Detail
Before :
sno full_name email
1 Ravi [email protected]
2 Ravi [email protected]
3 Ravi [email protected]
4 Pooja [email protected]
5 Pooja [email protected]
6 Radha [email protected]
7 John [email protected]
After :
sno full_name email
1 Ravi [email protected]
2 Pooja [email protected]
3 Radha [email protected]
4 John [email protected]
Suppose you want to delete all the duplicate records on the basis of “email” column.
Solution :
You should have atleast one identity key or unique column in your table, I am assuming “sno” as a unique column here.
Take backup (make a copy of your table), using “select * into Bak_Personal_Detail from Personal_Detail”
SQL Statement to delete duplicate records :
DELETE
FROM Personal_Detail
WHERE sno NOT IN
(
SELECT MAX(sno)
FROM Personal_Detail
GROUP BY email
)
The above statement will keep distinct records by grouping “email” field and delete rest of the duplicate records. After executing the statement, you should reset “sno” column to get serial identity in order. (Simply delete and create new identity column (sno) using modify table option).
Note :
Tested on MS-SQL 2005.
Can be tried on MY-SQL (any version)
