0

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 !!!

4
Contributors
3
Replies
4
Views
8 Years
Discussion Span
Last Post by alit2002
0

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.

0
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.........................

0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.