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 !!!
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.
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.........................
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
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.