There are times when duplicate rows somehow creep into the table. The best scenario to happen this is when the data is to be imported from some other table or data and the Constraints are removed so that data import successfully.
Now in Oracle you can delete the duplicate entries by just executing a simple SQL because Oracle stores an index to each row in a table known as ROWID
I create a table with just 2 fields. I dont have any constraint on ID because I would import some data which may be duplicated but after importing I would like to make the ID as PRIMARY KEY
Insert some date to simulate duplicates
Insert them more than once.
SQL to delete the duplicate rows
Explanation :- In the subquery I Select a unique rowid from all the rowid's It can be MAX, MIN any one and the group by clause should include all the UNIQUE columns I desire. Say I want a composite primary key then the group by should be group by id1,id2 ...
The the subquery returns one record for the dupliacte ID's and I delete all of them that are not in the subquery and that deletes all the dupliacte rows from the database.
Now in Oracle you can delete the duplicate entries by just executing a simple SQL because Oracle stores an index to each row in a table known as ROWID
Code: SQL
CREATE TABLE duptest
( Id varchar(5),
nonid varchar(5));
Insert some date to simulate duplicates
Code: SQL
INSERT INTO duptest VALUES('1','a');
Code: SQL
INSERT INTO duptest VALUES('2','b');
Code: SQL
DELETE FROM duptest WHERE rowid NOT IN (SELECT max(rowid) FROM duptest GROUP BY id);
The the subquery returns one record for the dupliacte ID's and I delete all of them that are not in the subquery and that deletes all the dupliacte rows from the database.
