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
CREATE TABLE duptest ( Id VARCHAR(5), nonid VARCHAR(5));
INSERTINTO duptest VALUES('1','a');
INSERTINTO duptest VALUES('2','b');
DELETEFROM duptest WHERE rowid NOTIN(SELECT max(rowid)FROM duptest GROUPBY 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.
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);
No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Unsolved 
