René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

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;