Query to Delete Duplicate Records from a Table

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)

Comments

[Vidhyadhar]

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]

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.