Delete duplicate records from table

Oracle : Delete duplicate records from table

8 years ago by Vinod SiramOracle Adhoc SQL Scripts

Delete duplicate records from table Dear Readers, In this article we will see ”Delete duplicate records from table” Script if one column having duplicate records:

DELETE from scott.test1 a0
WHERE a0.rowid NOT IN (
SELECT max(a1.rowid)
FROM scott.test1 a1
GROUP BY a1.a
HAVING count(*) > 1)
AND (a0.a) IN
(SELECT a2.a FROM scott.test1 a2
GROUP BY a2.a HAVING count(*) > 1);

DELETE from scott.test1 a0

WHERE a0.rowid NOT IN (

SELECT max(a1.rowid)

FROM scott.test1 a1

GROUP BY a1.a

HAVING count(*) > 1)

AND (a0.a) IN

(SELECT a2.a FROM scott.test1 a2

GROUP BY a2.a HAVING count(*) > 1);

Script if two columns having duplicate records:

DELETE from scott.test2 a0
WHERE a0.rowid NOT IN (
SELECT max(a1.rowid)
FROM scott.test2 a1
GROUP BY a1.NUM, a1.NAME
HAVING count(*) > 1)
AND (a0.NUM, a0.name) IN
(SELECT
a2.NUM,
a2.name
FROM scott.test2 a2
GROUP BY a2.NUM, a2.NAME
HAVING count(*) > 1);

DELETE from scott.test2 a0

WHERE a0.rowid NOT IN (

SELECT max(a1.rowid)

FROM scott.test2 a1

GROUP BY a1.NUM, a1.NAME

HAVING count(*) > 1)

AND (a0.NUM, a0.name) IN

(SELECT

a2.NUM,

a2.name

FROM scott.test2 a2

GROUP BY a2.NUM, a2.NAME

HAVING count(*) > 1);

Deleting duplicate records involving multiple columns :

DELETE from EMP a0
WHERE a0.rowid NOT IN (
SELECT max(a1.rowid)
FROM EMP a1
GROUP BY a1.COL1, a1.COL2, a1.COL3, a1.COL4
HAVING count(*) > 1)
AND (a0.COL1, a0.COL2, a0.COL3, a0.COL4) IN
(SELECT
a2.COL1, a2.COL2, a2.COL3, a2.COL4
FROM EMP a2
GROUP BY a2.COL1, a2.COL2, a2.COL3, a2.COL4
HAVING count(*) > 1);