When working on a database it is possible that you have to encounter the presence of duplicate records within the tables. Oracle databases allow you to locate and eliminate duplicate records using the "RowID" field. Before making such a radical change to a table, it is always a good idea to make a complete backup of it, so that you can go back to the deleted records if necessary.
Steps
Part 1 of 4: Identifying Duplicate Records
Step 1. Find all duplicate records in the table under consideration
In this example article we will look at the records related to the name "Alan". Check for actual duplicate records using the SQL query shown at the end of this section of the article.
Step 2. In this example, the discriminating column that allows you to identify duplicate records is the "Name" column
For this reason the "column_name" parameter of the SQL query must be replaced with the value "Name".
Step 3. Use other columns of the table to find duplicate records
For example, if you need to use the column containing the age instead of the name, you will need to replace the "column_name" parameter with the value "Age" and so on, depending on the nature of the data you need to manipulate.
select column_name, count (column_name) from table table_name group by column_name having count (column_name)> 1;
Part 2 of 4: Delete a Single Duplicate Record
Step 1. Select all the records of the table under consideration based on the discriminant column
After the command prompt identified by the acronym "SQL", which means "Standard Query Language", type the following query "select [column_name] from [table_name]".
Step 2. Delete all records related to the sample duplicate name
After the "SQL" prompt enter the query "delete from names where name = 'Alan';". It should be noted that in this case the use of capital letters is very important. The query used in this case will delete only the records related to the name "Alan". At this point type the command "commit" and press the "Enter" key.
Step 3. Insert the original record
Now that you have deleted all the records related to the name "Alan", you can proceed to insert only one using the following query "insert into name values ('Alan');". Again, after running the query, type the command "commit" and press the "Enter" key to physically create the new record.
Step 4. View the list of records present in the "name" table after changes
After completing the steps described in this section correctly, check the contents of the table in question to make sure it does not contain duplicate elements. Use the following query "select * from names".
SQL> select name from names; NAME ------------------------------ Alan Carrie Tom Alan rows selected. SQL> delete from names where name = 'Alan'; rows deleted. SQL> commit; Complete commit. SQL> insert into names values ('Alan'); row created. SQL> commit; Complete commit. SQL> select * from names; NAME ------------------------------ Alan Carrie Tom rows selected.
Part 3 of 4: Deleting Multiple Duplicate Records
Step 1. In this case, as a discriminant to identify duplicate records, refer to the "RowID" column of the table in question
After the "SQL" prompt, enter the query "select rowid, name from names;".
Step 2. Delete duplicate records
Use the following query "delete from names a where rowid> (select min (rowid) from names b where b.name = a.name);" to find and delete all duplicate records.
Step 3. Check again for duplicate records within the table under consideration
After completing the previous steps correctly, check if there are still duplicate records inside the example table "names". Use the following SQL query "select rowid, name from names;". After checking, enter the command "commit" and press the "Enter" key to consolidate the changes.
SQL> select rowid, name from names; ROWID NAME ------------------ ------------------------------ AABJnsAAGAAAdfOAAA Alan AABJnsAAGAAAdfOAAB Alan AABJnsAAGAAAdfOAAC Carrie AABJnsAAGAAAdfOAAD Tom AABJnsAAGAAAdfOAAF Alan rows selected. SQL> delete from names a where rowid> (select min (rowid) from names b where b.name = a.name); rows deleted. SQL> select rowid, name from names; ROWID NAME ------------------ ------------------------------ AABJnsAAGAAAdfOAAA Alan AABJnsAAGAAAdfOAAC Carrie AABJnsAAGAAAdfOAAD Tom rows selected. SQL> commit; Complete commit.
Part 4 of 4: Eliminating Duplicate Records Using Table Columns
Step 1. View the list of records in the example "names" table
After the "SQL" prompt, enter the following query "select * from names;". A list of all the records in the "names" table (and related columns) will be displayed.
Step 2. Eliminate duplicate records by identifying them based on table columns
Enter the following query "delete from names a where rowid> (select min (rowid) from names b where b.name = a.name and b.age = a.age);" after the "SQL" prompt to delete all duplicate records.
Step 3. Check again for duplicate records within the table under consideration
After completing the previous steps correctly, check if there are still duplicate records in the "names" example table. Use the following SQL query "select * from names;". After checking, enter the command "commit" and press the "Enter" key to consolidate the changes.
SQL> select * from names; NAME AGE ------------------------------ ---------- Alan 50 Carrie 51 Tom 52 Alan 50 rows selected. SQL> delete from names a where rowid> (select min (rowid) from names b where b.name = a.name and b.age = a.age); row deleted. SQL> select * from names; NAME AGE ------------------------------ ---------- Alan 50 Carrie 51 Tom 52 rows selected. SQL> commit; Complete commit.
Warnings
-
Create a full backup of the table using your account, so that you can show what you have deleted in case you need to justify your actions. Use this SQL code:
SQL> create table [backup_table_name] as select * from [original_table_name]; Table created.
