CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Domnic
Domnic

Reputation: 3867

Delete duplicate records from SQL Server where all values are the same except primary key

My table contains more than 10 columns.

For example:

id   name    date        status   rec_id   ..... etc
-------------------------------------------------------
 1   ssss    01/01/2000  Active    3      .....
 2   ssss    01/01/2000  Active    3      .....
 3   aaaa    11/01/2002  Active    5      .....
 4   aaaa    11/01/2002  Active    5      .....

I want to delete duplicate records in this table and I want results like below

     1     ssss    01/01/2000  Active    3      .....
     3     aaaa    11/01/2002  Active    5      .....

How do we achieve this?

I can delete duplicate records based on id like below

delete from test 
where ID not in (select min(ID)
                 from test 
                 group by name);

it got deleted. But I want to make sure the above way is correct ?

Upvotes: 1

Views: 48

Answers (2)

vamsi
vamsi

Reputation: 352

Use can use a CTE as shown below For the column list use all the columns on which you want to have a unique record. Do not include your ID column in the list of columns in CTE 

WITH DuplicateCTE  
    AS
    (
    SELECT   column1,column2,..,lastcolumn
    ROW_NUMBER() OVER
    (PARTITION BY   column1,column2,..,lastcolumn ORDER BY column1) AS DuplicateCount
    FROM yourtablename
    )  
    DELETE FROM DuplicateCTE WHERE DuplicateCount > 1
    GO

Upvotes: 1

D Stanley
D Stanley

Reputation: 152556

Yes your query is on the right track ,but you need to include ALL columns that define a duplicate, not just name:

DELETE FROM test 
WHERE ID NOT IN
( SELECT MIN(ID) FROM TEST
GROUP BY name, date, rec_id, status , {etc} )

Upvotes: 1

Related Questions