delete duplicate rows with Oracle SQL

set feedback off

create table T (col1 varchar2(5), col2 varchar2(2));


insert into T values ('a','a');
insert into T values ('a','a');

insert into T values ('a','z');
insert into T values ('a','z');

insert into T values ('a',null
);
insert into T values ('a',null
);

insert into T values ('m','q');
insert into T values ('m','q');

insert into T values ('m',null
);

insert into T values ('s','c');
insert into T values ('s','c');
insert into T values ('s','c');

insert into T values ('s','x');

insert into T values ('v','u');


delete from T t1
where t1.rowid
 > 
             ( select min(t2.rowID) from T t2
               where t1.col1 = t2.col1
               and t1.col2 = t2.col2);


select * from t;

drop table t;


//================================
It's easy to introduce duplicate rows of data into Oracle tables by 
running a data load twice without the primary key or unique indexes 
created or enabled.


Here's how you remove the duplicate rows before the primary key or unique indexes can be created:
 
DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3... ;
  
Here column1, column2, column3 constitute the identifying key for each record.
 


Be sure to replace our_table
 with the table name from which 
you want to remove the duplicate rows. The GROUP BY is used on the 
columns that make the primary key for the table. This script deletes 
each row in the group after the first row.