CODEWITHSUNDEEP
sqloracle-databaseduplicates
user3816289
user3816289

Reputation: 3

Remove Duplicate Rows in Oracle DB

I searched on the internet but could not find any explanation for my question below. The following code is not working when I try to remove duplicate rows from a test table. The test table has no unique or primary keys on it, and is defined as test(id number). Can you explain me why it is not working?

delete from test a 
where a.rowid in (select b.rowid 
                  from test b 
                  where b.id = a.id 
                  and b.rowid != a.rowid);

Upvotes: 0

Views: 225

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

The normal way this query would work is more like:

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

Your query is removing no rows because of the correlation. You are asking for a.rowid being in a list where it is specifically excluded by b.rowid != a.rowid. Hence, no rows can match this condition -- of being in a list they are specifically excluded from.

Upvotes: 2

dips
dips

Reputation: 76

This should help:

delete from test where rowid in
(
    select rowid from
      (select rowid,rank() over (partition by id order by rowid) rank 
       from test)temp_test 
    where temp_test.rank >1
);

Upvotes: 1

Related Questions