Deleting Duplicate Rows

Published on August 16 2015 by LakshmiSaahul,Dhana Royal

Deleting Duplicate Rows in Oracle

CREATE TABLE tbl_test(
SER_NO NUMBER,
FST_NM VARCHAR2(30),
DEPTID NUMBER,
CMNT VARCHAR2(30));

INSERT INTO tbl_test VALUES(1, 'aaaaa', 2004, 'xxx');
INSERT INTO tbl_test VALUES(2, 'bbbbb', 2005, 'yyy');
INSERT INTO tbl_test VALUES(1, 'aaaaa', 2004, 'xxx');
INSERT INTO tbl_test VALUES(1, 'aaaaa', 2004, 'xxx');
INSERT INTO tbl_test VALUES(3, 'ccccc', 2005, 'zzz');
INSERT INTO tbl_test VALUES(2, 'bbbbb', 2005, 'yyy');
1. Using MIN(rowid) : The most common method of removing duplicate rows.

DELETE FROM tbl_test
WHERE ROWID NOT IN (SELECT MIN (ROWID)
FROM tbl_test
GROUP BY ser_no, fst_nm, deptid, cmnt);
Comment: This will take hours & hours if the table is large (records in million).

2. Using MIN(rowid) & Join: More or less the same as first one

DELETE FROM tbl_test t
WHERE t.ROWID NOT IN (SELECT MIN (b.ROWID)
FROM tbl_test b
WHERE b.ser_no = t.ser_no
AND b.fst_nm = t.fst_nm
AND b.deptid = t.deptid
AND b.cmnt = t.cmnt);
3. Using Subquery: This is an interesting one

DELETE FROM tbl_test
WHERE ser_no IN (SELECT ser_no FROM tbl_test GROUP BY ser_no, fst_nm, deptid, cmnt HAVING COUNT (*) > 1)
AND fst_nm IN (SELECT fst_nm FROM tbl_test GROUP BY ser_no, fst_nm, deptid, cmnt HAVING COUNT (*) > 1)
AND deptid IN (SELECT deptid FROM tbl_test GROUP BY ser_no, fst_nm, deptid, cmnt HAVING COUNT (*) > 1)
AND cmnt IN (SELECT cmnt FROM tbl_test GROUP BY ser_no, fst_nm, deptid, cmnt HAVING COUNT (*) > 1)
AND ROWID NOT IN (SELECT MIN (ROWID)
FROM tbl_test
GROUP BY ser_no, fst_nm, deptid, cmnt
HAVING COUNT (*) > 1)
Comment: A complicated way of performing the same task. Not efficient.

4. Using Nested Subqueries:

DELETE FROM tbl_test a
WHERE (a.ser_no, a.fst_nm, a.deptid, a.cmnt) IN (SELECT b.ser_no, b.fst_nm, b.deptid, b.cmnt
FROM tbl_test b
WHERE a.ser_no = b.ser_no
AND a.fst_nm = b.fst_nm
AND a.deptid = b.deptid
AND a.cmnt = b.cmnt
AND a.ROWID > b.ROWID);
Comment: Will work but for large tables, this is not efficient.

5. Using Analytic Fucntions:

DELETE FROM tbl_test
WHERE ROWID IN (
SELECT rid
FROM (SELECT ROWID rid,
ROW_NUMBER () OVER (PARTITION BY ser_no, fst_nm, deptid, cmnt ORDER BY ROWID) rn
FROM tbl_test)
WHERE rn <> 1);
Comments: This is by far one of the best solutions if the table is really really large. Using the invaluable power of Analytics.

Advertising