Delete duplicate rows - Oracle

Question

Here is an example of some of my data. I am trying to remove all extra data where the QueryID and RoyID are the same as other entries in the table, however, I would like to keep atleast one record. I'm not sure I'll be able to use the min or max functions on the ID column as some of the records have IDs like DEMO_12345 etc.

ID          QUERY_ID ROYALTY_ID
RTSQR1652   SQ1421  ROY25644005
RTSQR1653   SQ1421  ROY25636406
RTSQR1654   SQ1421  ROY25636557
RTSQR1655   SQ1421  ROY25636558
RTSQR1656   SQ1421  ROY25636559
RTSQR1657   SQ1421  ROY25636560

I was thinking to use ROWID instead of ID. Would the following query work?

 DELETE FROM RT_SOURCE_QUERY_ROYALTIES WHERE ROWID NOT IN (
 SELECT MAX(ROWID) FROM RT_SOURCE_QUERY_ROYALTIES GROUP BY ROYALTY_ID, QUERY_ID);

possible duplicate of [Removing duplicate rows from table in Oracle](http://stackoverflow.com/questions/529098/removing-duplicate-rows-from-table-in-oracle) — Bob Jarvis - Слава Україні, Oct 05 '12 at 10:42

score 7 · Accepted Answer · answered Oct 05 '12 at 10:43

7

Just run a test

drop table test1;

create table test1 (a number,b number, c number);

insert into test1 values (1,1,2);
insert into test1 values (1,1,3);
insert into test1 values (1,2,2);
insert into test1 values (2,1,2);
insert into test1 values (2,2,2);
insert into test1 values (1,1,2);

select * from test1;

delete from test1 where rowid not in (select max(rowid) from test1 group by a,b);

select * from test1;

Works as expected, doesn't it ...

table TEST1 dropped.
table TEST1 created.
1 rows inserted.
1 rows inserted.
1 rows inserted.
1 rows inserted.
1 rows inserted.
1 rows inserted.
A B C
- - -
1 1 2 
1 1 3 
1 2 2 
2 1 2 
2 2 2 
1 1 2 

 6 rows selected 

2 rows deleted.
A B C
- - -
1 2 2 
2 1 2 
2 2 2 
1 1 2

answered Oct 05 '12 at 10:43

Rob van Laarhoven

8,737
2
31
49

How is rowid (order of records) from delete related to rowid from "select max(rowid) from test1 group by a,b" ? select without order by can return records in any order DB likes. There is no grantee. So, seems like you could delete wrong records. – pero Oct 05 '12 at 10:49
2

rowid (not rownum) has nothing to do with 'order of records' it's an technical representation of where the record is stored. The OP wanted to keep just one record, which one is unspecified, not important. – Rob van Laarhoven Oct 05 '12 at 11:06

score 1 · Answer 2 · answered Oct 05 '12 at 10:41

http://www.dba-oracle.com/t_delete_duplicate_table_rows.htm

Use self-join to delete duplicate rows

The most effective way to detect duplicate rows is to join the table against itself as shown below.

select 
   book_unique_id, 
   page_seq_nbr, 
   image_key 
from 
   page_image a 
where 
   rowid > 
     (select min(rowid) from page_image b 
      where 
         b.key1 = a.key1 
      and 
         b.key2 = a.key2 
      and 
         b.key3 = a.key3 
      );

Please note that you must specify all of the columns that make the row a duplicate in the SQL where clause.

Delete duplicate rows - Oracle

2 Answers2