Oracle

How to delete duplicate entries in a table ?

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Aug 2008

Posts: 1

Reputation: evk_87 is an unknown quantity at this point

Solved Threads: 0

evk_87

Offline

Newbie Poster

How to delete duplicate entries in a table ?

Sep 8th, 2008

In oracle 9i ........How to delete duplicate entries in a table ?

if suppose in a table named office, one or more entry(row) is repeated more then twice or minimum twice.
I need a query to delete the multiple entries ....

Note:
--->No constraints applied on the table.
--->No Primary Key
--->You cannot create any object....that is no view or a duplicate table can be created

Plz help !!!

•

Join Date: Nov 2004

Posts: 6,145

Reputation: jwenting is just really nice

Solved Threads: 212

jwenting

Offline

duckman

Re: How to delete duplicate entries in a table ?

Sep 10th, 2008

we're not here to do your homework for you, and given your "requirements" it's homework as no real job would restrict you like that.

As people are clearly allowed to attack me but I'm not allowed to defend myself, I no longer post to this site.

•

Join Date: Dec 2008

Posts: 9

Reputation: like_to_learn is an unknown quantity at this point

Solved Threads: 0

like_to_learn like_to_learn is offline

Offline

Newbie Poster

Re: How to delete duplicate entries in a table ?

Dec 10th, 2008

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
CREATE TABLE duptest  ( Id VARCHAR(5),  nonid VARCHAR(5));
INSERT INTO duptest VALUES('1','a');
INSERT INTO duptest VALUES('2','b');
DELETE FROM duptest WHERE rowid NOT IN (SELECT max(rowid) FROM duptest GROUP BY id);
CREATE TABLE duptest  ( Id varchar(5),  nonid varchar(5));
INSERT INTO duptest VALUES('1','a');
INSERT INTO duptest VALUES('2','b');
DELETE FROM duptest WHERE rowid NOT IN (SELECT max(rowid) FROM duptest GROUP BY id);

Then you can get the unique records.........................

Last edited by peter_budo; Dec 12th, 2008 at 7:12 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Dec 2006

Posts: 44

Reputation: alit2002 is an unknown quantity at this point

Solved Threads: 1

alit2002

Offline

Light Poster

Re: How to delete duplicate entries in a table ?

Dec 24th, 2008

The following SELECT statement will find and display all duplicate rows
in a table, except the row with the maximum ROWID. The example uses the
dept table:

SELECT * FROM dept a
WHERE ROWID <> (SELECT MAX(ROWID)
FROM dept b
WHERE a.deptno = b.deptno
AND a.dname = b.dname -- Make sure all columns are compared
AND a.loc = b.loc);

The following statement will delete all duplicate rows in a table, except the
row with the maximum ROWID:

DELETE FROM dept a
WHERE ROWID <> (SELECT MAX(ROWID)
FROM dept b
WHERE a.deptno = b.deptno
AND a.dname = b.dname -- Make sure all columns are compared
AND a.loc = b.loc);

Alternatively:

DELETE FROM dept a
WHERE 1 < (SELECT COUNT(deptno)
FROM dept b
WHERE a.deptno = b.deptno
AND a.dname = b.dname -- Make sure all columns are compared
AND a.loc = b.loc);

Merry Christmas
Alistair