Duplicate Records

Somehow i got a duplicate recorded into one of my tables.

Is there any way I can edit them, or will I have to delete both of the records and re-insert the correct records?

• Back up first. Deleting records can cause several unprintable sounds to be uttered by the user/DBA who has just executed it.

• Define duplicate. A duplicate is only identifiable if the uniqueness attributes have been well defined. This is important especially where multiple columns constitute the uniqueness attributes set.

• A delete statement which also works for multiple uniqueness columns is :

Expand|Select|Wrap|Line Numbers

1. DELETE FROM table_name
2. WHERE rowid NOT IN
3. (SELECT MIN(rowid)
4. FROM table_name
5. GROUP BY col1, col2, col3... ;
6.  

Here col1, col2, e.t.c are the uniqueness columns.rowid is a special built in oracle "column" (pseudo-column) which uniquely identifies each row in a table. So every Oracle row has got it.
Note that rowid should not be used as part of the data of a row because even though it is unique, it changes every time you do EXP,IMP or in indexed and partitioned tables.


The inner select retrieves all the duplicates (as defined by the uniqueness attributes col1,col2, e.tc).
The delete then deletes all of them except the one with the least rowid.

• Usually your table design should be structured in such a way that duplicates should not be possible. However, sometimes ghosts fly in the DB e.t.c. It's important though to recheck your table design everytime you see unintended duplicates.