How to Delete duplicate record from table in sql server 2008

Question

Hi all I have a table with two field age location 20 A 22 B 20 A 22 B 23 C Now i want to delete duplicate record from table through sql query if any one know then please help me. Thanks in advance.....................

Satyapriya Nayak · Answer

DELETE FROM TestTable WHERE TestTable.%%physloc%% NOT IN (SELECT MIN(b.%%physloc%%) FROM TestTable b GROUP BY b.column1 b.Column2); http://www.codeproject.com/Tips/159881/How-to-remove-duplicate-rows-in-SQL-Server-2008-wh

Sandeep Mhatre · Answer

you can refer this query /* Delete Duplicate records */ WITH CTE (Col1RowNum) AS ( SELECT Col1 ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col1) AS RowNum FROM DuplicateRcordTable ) DELETE FROM CTE WHERE RowNum > 1 GO http://sandeepmhatre.blogspot.com/2013/03/common-table-expressions.html

venkata kumar · Answer

hi try this DELETE FROM MyTable WHERE ID NOT IN ( SELECT MAX(ID) FROM MyTable GROUP BY DuplicateColumn1 DuplicateColumn2 DuplicateColumn3) DELETE FROM MyTable WHERE ID NOT IN ( SELECT MAX(ID) FROM MyTable GROUP BY DuplicateColumn1 DuplicateColumn2 DuplicateColumn3)