How to Delete Duplicate records from Table in SQL Server?

Question

How to Delete Duplicate records from Table in SQL Server ?

Answer 1

WITH CTE AS(
   SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
       RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1)
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1

Example

Answer 2

To delete rows where the combination of columns col_1, col_2, ... col_n are duplicates, you can use a common table expression;

WITH cte AS (
  SELECT *, 
         ROW_NUMBER() OVER (PARTITION BY col1, ..., col_n ORDER BY col_1) AS rn 
  FROM table_1
)
DELETE FROM cte WHERE rn<>1;

Since the rows are classified by the contents of the listed columns, if the rows are identical in all ways, you'll still need to list all columns in the query.

As always, test and/or backup before running deletes from random people on the Internet on your production data.

Answer 3

The following questions and the answer given there could be the best help for you

Remove Duplicate Records

You can select the min and max of the rowId (if there is and identity field otherwise add one)

DELETE MyTable 
FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, Col1, Col2, Col3 
   FROM MyTable 
   GROUP BY Col1, Col2, Col3
) as KeepRows ON
   MyTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL
Use

Source

Answer 4

Add an identity column to your table:

Alter table tbl_name add Id int identity(1,1)

Then run following query for deleting records from table:

Delete from tbl_Name where Id not in(select min(Id) from tbl_Name group by RowId)

Answer 5

//duplicate_data_table contains duplicate values 

create temp as (select distinct * from duplicate_data_table);

    drop duplicate_data_table;

create duplicate_data_table as (select * from temp);

    drop temp;