How to Remove Duplicate Rows from a Table

Use rowid pseudo column. All you have to do is to keep the latest data (i.e. highest ROWID) and remove other duplicated rows.

SELECT * FROM table1 a
WHERE rowid < (SELECT max(rowid) FROM table1 b
WHERE a.column1 = b.column1 AND etc...);

OR

create table testtt (num number);

insert into testtt values(111);
insert into testtt values(111);
insert into testtt values(111);
insert into testtt values(111);
insert into testtt values(222);
insert into testtt values(222);
insert into testtt values(333);
insert into testtt values(333);
insert into testtt values(333);

select * from testtt;

delete from testtt
where (rowid, num) not in (select max_rid, num
from (select num,
count(num) over (partition by num) cnt,
max(rowid) over (partition by num) max_rid
from testtt)
where cnt > 1);

select * from testtt;

OR

While I doubt this method has any advantages over another, it's an example:

DELETE FROM table_a
WHERE rowid IN
( SELECT rowid FROM table_a
MINUS
SELECT MAX( rowid ) FROM table_a
GROUP BY column_list )

OR

delete from table_name where rowid not in (select max(rowid) from table group byduplicate_values_field_name);

OR


highest rowid does not necessarily mean latest data... since space freed from deleting rows might be reused.

SQL> CREATE TABLE t AS SELECT level l FROM DUAL CONNECT BY LEVEL <= 5000;
SQL> DELETE FROM t WHERE l < 5000;
SQL> COMMIT;
SQL> INSERT INTO t VALUES (5001);
SQL> COMMIT;
SQL> SELECT max(l) KEEP(DENSE_RANK LAST ORDER BY rowid) as maxrid, max(l) KEEP(DENSE_RANK FIRST ORDER BY rowid) minrid FROM t;

MAXRID MINRID
---------- ----------
5000 5001

 

Leave a Comment