CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2012
Anyname Donotcare
Anyname Donotcare

Reputation: 11403

How to delete all the duplicates keeping only one of them

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;

Upvotes: 0

Views: 91

Answers (2)

Rohit Gupta
Rohit Gupta

Reputation: 455

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;

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28930

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;

Upvotes: 3

Related Questions