Duplicate Records

[Stang02GT]

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?

[debasisdas]

try to use this sample code for reference
[code=oracle]Delete from emp e
where rowid>(select min(rowid) from emp
where e.empno=empno)[/code]

[amitpatel66]

you can try this as well:

[code=oracle]

delete from table_name where ROWID NOT IN (SELECT MAX(rowID) FROM table_name group by empno)

[/code]

This will delete older records

[r035198x]

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?

Elaborating a bit on the replies given so far. The important things to note when trying to get rid of duplicate records are :

• 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 :

[CODE=mysql]
DELETE FROM table_name
WHERE rowid NOT IN
(SELECT MIN(rowid)
FROM table_name
GROUP BY col1, col2, col3... ;
[/CODE]

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.