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.....................

Answer 1

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

Answer 2

you can refer this query

/* Delete Duplicate records */

WITH CTE (Col1,RowNum)

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

Answer 3

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)