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…

Advertisements


No Responses Yet to “How To Delete Duplicate Rows in Database Table”

  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: