Overblog Follow this blog
Edit post Administration Create my blog
Balavardhan Reddy Narani

Query to Delete Duplicate Records from a Table

March 7 2009 , Written by Balavardhan Published on #SQL Server2005

Delete Duplicate Records From a Table

Table Structure

Name : Employee
Columns : Emp_ID Identity Column
EMP_Name
DOB
SEX


if you want delete the Duplicate Records based on EMP_Name Column which was existed Previously (i.e. Delete Duplicate Emp_name records except which has Max(Emp_ID))

Delete From Employee Where EMP_ID Not in (
Select Max(EMP_ID) From Employee Where EMP_Name in
(Select EMP_Name From Employee
Group by EMP_Name
Having Count(*) > 1)
group by EMP_Name)

Share this post

Repost 0
To be informed of the latest articles, subscribe:

Comment on this post

Vidhyadhar 07/08/2009 16:33

Oye manishiyou need to append the following to your query mentioned above. Becoz it will delete the duplicates as well as single records (non duplicates) also.        AND EMP_ID NOT IN        (        SELECT MAX(EMP_ID) FROM @TEMPTABLE        WHERE EMP_Name IN (SELECT EMP_Name FROM @TEMPTABLE GROUP BY EMP_Name HAVING COUNT(*) = 1)        GROUP BY EMP_Name        )Finally query would be the following:DELETE FROM EMPLOYEE WHERE EMP_ID NOT IN (                    SELECT MAX(EMP_ID)                     FROM EMPLOYEE                     WHERE EMP_NAME IN                            (SELECT EMP_NAME FROM EMPLOYEE                            GROUP BY EMP_NAME                            HAVING COUNT(*) > 1)                    GROUP BY EMP_NAME)    AND EMP_ID NOT IN    (    SELECT MAX(EMP_ID) FROM @TEMPTABLE    WHERE EMP_Name IN (SELECT EMP_Name FROM @TEMPTABLE GROUP BY EMP_Name HAVING COUNT(*) = 1)    GROUP BY EMP_Name    )

Balavardhan Reddy 07/09/2009 06:36


Hi..

I had given query to Delete duplicate Records.. It means.. if you have 5 records for a Same name, then it deletes 4 records and it keeps 1 Record. So It not become duplicate one.