CODEWITHSUNDEEP
sqlsql-server
Srinivas
Srinivas

Reputation: 339

How to Delete Duplicate records from Table in SQL Server?

How to Delete Duplicate records from Table in SQL Server ?

Upvotes: 1

Views: 576

Answers (5)

Mark
Mark

Reputation: 1

//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;

Upvotes: 0

Mike Clark
Mike Clark

Reputation: 1870

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)

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180867

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.

Upvotes: 3

Rohit Chaudhari
Rohit Chaudhari

Reputation: 757

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

Upvotes: 2

Trikaldarshiii
Trikaldarshiii

Reputation: 11314

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

Upvotes: 0

Related Questions