Deleting duplicate record from table - SQL query

Question

I need to delete duplicate rows only from the table, like I have 3 duplicate rows in the table, my query will delete 2 rows from 3 duplicated rows.

How can I get this? Please help me.

Answer 1

Please try the below query, it will definitely meet your objective

SET ROWCOUNT 1
DELETE test
FROM test a
WHERE (SELECT COUNT(*) FROM test b WHERE b.name = a.name) > 1
WHILE @@rowcount > 0
  DELETE test
  FROM test a
  WHERE (SELECT COUNT(*) FROM test b WHERE b.name = a.name) > 1
SET ROWCOUNT 0

where test is your table name