How to delete all the duplicates keeping only one of them

Question

I want to delete all the duplicates keeping only one of them.

My query should delete all the duplicated data :

WITH todelete As (
      select em.*,
             row_number() over (partition by emp_num, [from_date],[to_date],[ req_ser], [ req_year]
                                order by (select null)) as cnt
      from empmission em
)

DELETE FROM todelete
WHERE cnt > 2;

Actually, it should leave two duplicates. Your where clause should be `WHERE cnt > 1`. — Zohar Peled, Jul 31 '16 at 09:37

score 3 · Answer 1 · edited May 23 '17 at 12:17

As stated by Zohar in comments,your query will keep two duplicate rows not one..To keep only one row change your query like below..

Check out this question and all its answers for several ways to delete like rownumber,group by ,self join and also optimization tips on when to use group by vs rownumber..

WITH todelete As (
      select em.*,
             row_number() over (partition by emp_num, [from_date],[to_date],[ req_ser], [ req_year]
                                order by (select null)) as cnt
      from empmission em
)

DELETE FROM todelete
WHERE cnt > 1;

score 1 · Accepted Answer · edited Apr 13 '17 at 12:42

First Of all I suggest you not to use CTE use temp table for your solution use the below link for finding why I ma saying this

https://dba.stackexchange.com/questions/13112/whats-the-difference-between-a-cte-and-a-temp-table

to know more use the below link

http://www.dotnet-tricks.com/Tutorial/sqlserver/X517150913-Difference-between-CTE-and-Temp-Table-and-Table-Variable.html

Now lets us get back to your solution, i have used Temp Table

Select e.emp_num,e.from_date,e.to_date,e.[ req_ser],e.[ req_year],COUNT(1) number_of_duplicates
into #temp1 from empmission  e
group by emp_num,from_date,to_date,[ req_ser],[ req_year]

Truncate Table empmission 


 Insert into empmission 
 Select t.emp_num,
       t.from_date, 
       t.to_date,
       t.[ req_ser],
       t.[ req_year]
     from #temp1 t
   Drop Table #temp1      --Droping the temp table

  Select * from empmission

Or You can go with CTE As

WITH todelete As (
  select em.*,
         row_number() over (partition by emp_num, [from_date],[to_date],[ req_ser], [ req_year]
                            order by (select null)) as cnt
  from empmission em
  )
  DELETE FROM todelete
WHERE cnt > 1;

How to delete all the duplicates keeping only one of them

2 Answers2