Delete duplicate rows or one of the double records


Many a time we face cases where we have to delete one of double record.

So what can we do? Here an example of it,

SQL> select * from customer;

LAST_NAME FIRST_NAME NATION
—————————————–
XHI WILLIAM INDONESIA
BRUNI CARLA FRANCE
THERON KEIRA ENGLAND
PITT DAVID USA
PITT DAVID USA

To get the double record,

SELECT last_name, first_name
FROM customer
GROUP BY last_name, first_name
HAVING count(‘x’) > 1;

LAST_NAME FIRST_NAME
————————–
PITT DAVID

To delete one of the double record, (please add some filter to specify which record can be deleted)
1. standard
DELETE FROM CUSTOMER A
WHERE ROWID (
SELECT MAX(ROWID)
FROM customer B
WHERE A.last_name = B.last_name
AND A.first_name = B.first_name
AND A.nation = B.nation
);

2. using IN
DELETE FROM CUSTOMER A
WHERE (A.last_name, A.first_name, A.nation) IN (
SELECT B.last_name, B.first_name, B.nation
FROM customer B
WHERE A.last_name = B.last_name
AND A.first_name = B.first_name
AND A.nation = B.nation
AND A.rowid > B.rowid);

3. For the better query use “EXISTS” instead of “IN”,

DELETE FROM CUSTOMER A
WHERE EXISTS (
SELECT ‘x’
FROM customer B
WHERE A.last_name = B.last_name
AND A.first_name = B.first_name
AND A.nation = B.nation
AND A.rowid > B.rowid);

2 Responses

  1. thanx..

  2. […] Posted on July 15, 2008 by pandazen http://pandazen।wordpress.com/2008/07/15/delete-one-of-the-double-records/  […]

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: