How To Delete Duplicate Rows in Database Table

05Jul11

I had a situation today, I accidentally executed SQL insert script twice. The bad thing was that the table had no primary key or unique key defined which may have stopped it from execution.

Lesson: Not having Primary keys is a bad idea

So, now I can not delete each row manually so I had to write a query which could do that for me. Thanks god it saved me a lot of time.

So here goes the query, rather queries for performing the same functionality.

First Method

DELETE FROM department
WHERE rowid not in
(
SELECT MIN(rowid)
FROM department d
GROUP BY D.department_id
)

Second Method

DELETE FROM department A
WHERE ROWID > (
SELECT min(rowid)

FROM department B
WHERE A.department_id = B.department_id);

 

Third Method

DELETE FROM department A
WHERE EXISTS
(
SELECT ‘x’
FROM department B
WHERE A.department_id = B.department_id
AND B.rowid      > A.rowid);

I hope it helps.

 

Enjoy…

Filed under: Oracle, SQL, TOAD   |  Leave a Comment
Tags: database, delete duplicate, Oracle, SQL, sql delete, sql delete duplicate, TOAD