script to delete duplicates in a table

DELETE FROM emp
WHERE ROWID IN
(SELECT ROWID
FROM (SELECT ROWID,
ROW_NUMBER() OVER (PARTITION BY empno ORDER BY EMPNO) rn
FROM emp2)
WHERE rn > 1);

source:techrepublic

This is another script to delete duplicates in a table, using analytical functions.

The table is emp and the criteria for whether the row is duplicate is the empno.

If your version of Oracle does not have analytical functions, you can use this instead

DELETE FROM emp e1
WHERE ROWID !=
(SELECT min(rowid)
FROM emp e2
where e2.empno = e1.empno);

From the tests I ran, the second script is faster.

technorati tags:oracle, sql

Blogged with Flock

Links

• Subscribe to this feed