Background
This is classic database question to check candidates knowledge about SQL queries. You have a table where lets say you have duplicate entries (lets also say column1 and column2 can form a candidate key). Now you need to remove duplicates from then table. That is all rows in the table should be distinct. How would you do this?
Query to remove duplicate rows from table in Oracle
You can execute following query to remove duplicates -
DELETE FROM your_table WHERE rowid not in (SELECT MIN(rowid) FROM your_table GROUP BY column1, column2);
Example
Queries :
create table schema8.EMPLOYEE(ID int, name varchar2(255)); insert into schema8.EMPLOYEE values(1,'Aniket'); insert into schema8.EMPLOYEE values(1,'Aniket'); insert into schema8.EMPLOYEE values(1,'Aniket'); insert into schema8.EMPLOYEE values(2,'John'); insert into schema8.EMPLOYEE values(2,'John'); insert into schema8.EMPLOYEE values(3,'Sam'); insert into schema8.EMPLOYEE values(3,'Sam'); insert into schema8.EMPLOYEE values(3,'Sam');
ROWID Pseudocolumn in Oracle
For each row in the database, the ROWID pseudocolumn returns the address of the row. Oracle Database rowid values contain information necessary to locate a row.
Rowid values have several important uses:
- They are the fastest way to access a single row.
- They can show you how the rows in a table are stored.
- They are unique identifiers for rows in a table.
NOTE : You should not use ROWID as the primary key of a table. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later.
NOTE : Although you can use the ROWID pseudocolumn in the SELECT and WHERE clause of a query, these pseudocolumn values are not actually stored in the database. You cannot insert, update, or delete a value of the ROWID pseudocolumn.
