Duplicate rows in Oracle can only be differentiated by their ‘RowId’ (row address).
Steps
Step 1. Delete the row using the rowid
This is the easiest way to delete an entry.
SQL> select rowid, name from names; ROWID NAME ------------------ ------------------------------ AABJnsAAGAAAdfOAAA Alan AABJnsAAGAAAdfOAAB Alan AABJnsAAGAAAdfOAAC Carrie AABJnsAAGAAAdfOAAD Tom 4 rows selected. SQL> delete from names where rowid = 'AABJnsAAGAAAdfOAAA'; 1 row deleted. SQL> commit; Commit complete. SQL> select rowid, name from names; ROWID NAME ------------------ -------------------- AABJnsAAGAAAdfOAAB Alan AABJnsAAGAAAdfOAAC Carrie AABJnsAAGAAAdfOAAD Tom 3 rows selected.
Step 2. Remove all but one duplicate line
SQL> select name from names; NAME ------------------------------ Alan Carrie Tom Alan 4 rows selected. SQL> delete from names where name = 'Alan'; 2 rows deleted. SQL> commit; Commit complete. SQL> insert into names values ('Alan'); 1 row created. SQL> commit; Commit complete. SQL> select * from names; NAME ------------------------------ Alan Carrie Tom rows selected.
Step 3. Use the max or min function on rowed and remove all other rows
In this case, if multiple duplicates exist, they will be removed.
SQL> select rowid, name from names; ROWID NAME ------------------ ------------------------------ AABJnsAAGAAAdfOAAA Alan AABJnsAAGAAAdfOAAB Alan AABJnsAAGAAAdfOAAC Carrie AABJnsAAGAAAdfOAAD Tom AABJnsAAGAAAdfOAAF Alan 5 rows selected. SQL> delete from names a 2 where rowid> (select min (rowid) from names b 3 where b.name = a.name 4); 2 rows deleted. SQL> select rowid, name from names; ROWID NAME ------------------ ------------------------------ AABJnsAAGAAAdfOAAA Alan AABJnsAAGAAAdfOAAC Carrie AABJnsAAGAAAdfOAAD Tom 3 rows selected. SQL> commit; Commit complete. }}
Duplicate line 3 at the top and add a new column name if you have multiple columns in your table. Let's say you have a column of ages, then the command is next.
[[Image: Delete Duplicate Records in Oracle Step 4-j.webp" />
{{CodeBox | SQL> select * from names; NAME AGE ------------------------------ ---------- Alan 50 Carrie 51 Tom 52 Alan 50 4 rows selected. SQL> delete from names a 2 where rowid> (select min (rowid) from names b 3 where b.name = a.name 4 and b.age = a.age 5); 1 row deleted. SQL> select * from names; NAME AGE ------------------------------ ---------- Alan 50 Carrie 51 Tom 52 rows selected … SQL> commit; Commit complete.
Warnings
- Make a backup copy of the table to compare and show the contents of the table before deleting anything (if you have any questions).
SQL> create table alan.names_backup as select * from names; Table created.