Saturday, February 20, 2010

How to Remove duplicate records - Sql-server 2005

Query to remove duplicate records, in this query please add all the columns in GROUP BY clause that are duplicate, it is based on primary key


DELETE from TableName
WHERE CoumnName1 + CoumnName2 + CoumnName3
IN (select CoumnName1 + CoumnName2 + CoumnName3
        FROM TableName
        group by CoumnName1 , CoumnName2 ,CoumnName3
        having Count(ID)>1)
AND ID NOT IN (select MAX(ID) from TableName
        WHERE CoumnName1 + CoumnName2 + CoumnName3
        IN (select CoumnName1 + CoumnName2 + CoumnName3
                FROM TableName
                group by CoumnName1 , CoumnName2 ,CoumnName3
                having Count(ID)>1)
        GROUP BY CoumnName1 ,CoumnName2 ,CoumnName3)

No comments: