Tips and Tricks in a world of Mix


Quantcast

PDRTJS_settings_285008_post_78 = { “id” : “285008”, “unique_id” : “wp-post-78”, “title” : “Delete+duplicate+rows+or+one+of+the+double+records”, “item_id” : “_post_78”, “permalink” : “http%3A%2F%2Fpandazen.wordpress.com%2F2008%2F07%2F15%2Fdelete-one-of-the-double-records%2F” } 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);

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

Tag Cloud

%d bloggers like this: