Oracle : Delete duplicate records from table

Share via:

Post Views: 0

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);

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);

Thank you for giving your valuable time to read the above information.
Follow us on
Website : www.ktexperts.com
Facebook Page : KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin