CODEWITHSUNDEEP
sqloracle-database
Himanshu
Himanshu

Reputation: 3970

How to keep only the first unique element and delete its duplicates in oracle?

So this query looks like a very easy by its statement but in actual isnt that easy. Heres my code what ive tried. 

Delete from table where id
In (select id from (select 
 id, row_number() 
  over(partition by id) 
rn from table where rn>1)

The above can work but thats not standard sql for almost all databases like partition by may not be supported in most of the other databases. What i was trying was below is it possible using group by. I tried below but i am not sure this will work or not. Any suggestions and which one is optimized

  //using group by    
Delete from table where id
In (select id from(select 
 id from table 
  Group by id 
  Having sum(1)>1) 

  )

Upvotes: 0

Views: 105

Answers (2)

Littlefoot
Littlefoot

Reputation: 143003

As question says

delete its duplicates in oracle

then 

delete from your_table a
where a.rowid > (select min(b.rowid)
                 from your_table b
                 where b.id = a.id
                );

Upvotes: 2

Popeye
Popeye

Reputation: 35920

You can use exists as follows:

Delete from your_table t
Where exists (select 1 from your_table t1
Where t1.id = t.id
And t1.rowid > t.rowid)

Upvotes: 1

Related Questions