Delete duplicate records keeping original

Question

Possible Duplicate:
How to delete duplicate rows with SQL?

I have a table with no primary key and a column with duplicate entries. I want to delete all duplicates keeping one entry in the table. Please help

Answer 1

Since you are using SQL Server 2005+, you can use CTE to perform this:

;WITH cte AS 
(
  SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3  ORDER BY (SELECT 0)) RN
  FROM  yourtable
)
DELETE FROM cte
WHERE RN > 1

Answer 2

Use Row_Number function with Partition By all fields and keep only the rows with RN = 1.

Answer 3

Create Stored procedure and inside of procedure:

1. you have to create temp table from given table structure at dynamically
2. then insert all distinct row from table to temp table
3. finally truncate table
4. and atlast insert row from temp table and drop temp table

If you have problem then I have to write sp for you.