While working in Oracle, you can find duplicates on some records. You can remove duplicate rows by identifying them and using the corresponding RowID alias row address. Before starting, create a backup table in case you need a reference after the record is deleted.
Step
Method 1 of 4: Identifying Duplicates
Step 1. Identify duplicates
In this example, we identify the duplicate "Alan". Make sure the records to be deleted are indeed duplicates by entering the SQL below.
Step 2. Identify from the column titled "Name"
In case the column has the title "Name", you need to replace " column_name " with Name.
Step 3. Identify from other columns
If you are trying to identify duplicates from different columns, for example Alan's age instead of his name, enter “Age” in place of " column_name " and so on.
select column_name, count(column_name) from table group by column_name having count (column_name) > 1;
Method 2 of 4: Removing Single Duplicates
Step 1. Select " name from names"
After "SQL" (short for Standard Query Language), enter " select name from names ".
Step 2. Delete all rows with duplicate names
After "SQL", enter " delete from names where name ='Alan';." It should be noted that capitalization is important here so that this step can delete all the lines named "Alan". After "SQL", enter "commit"
Step 3. Re-enter rows without duplicates
Now that you've deleted all the rows and replaced them with "Alan", fill one back in by entering " insert into name values ('Alan');." After "SQL", enter " commit " to create a new line.
Step 4. View the new list
After completing the steps above, you can check to make sure there are no more duplicate records by entering " select * from names ".
SQL > select name from names; NAME ------------------------------ Alan Citra Tomi Alan Baris selected. SQL > delete from names where name='Alan'; The line is deleted. SQL > commits; \Commit complete. SQL > insert into names values('Alan'); row created. SQL > commits; Commit complete. SQL > select * from names; NAME ------------------------------ Alan Citra Tomi rows selected.
Method 3 of 4: Removing Multiple Duplicates
Step 1. Select the RowID you want to delete
After "SQL", enter " select rowid, name from names;."
Step 2. Remove duplicates
After "SQL", enter " delete from names a where rowid > (select min(rowid) from names b where b.name=a.name);" to remove duplicates.
Step 3. Check for duplicates
After completing the above steps, check for duplicates by entering " select rowid, name from names;" then "commit".
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 > commits; Commit complete.
Method 4 of 4: Deleting Rows by Columns
Step 1. Select the row
After "SQL", enter " select * from names;" to be able to see the line.
Step 2. Remove duplicate rows by identifying their columns
After "SQL'" enter " delete from names a where rowid > (select min(rowid) from names b where b.name=a.name and b.age=a.age);" to remove duplicate records.
Step 3. Check for duplicates
Once you have completed the steps above, enter " select * from names;" then " commit " to see if the duplicates have actually been removed.
SQL > select * from names; NAME AGE ------------------------------ ---------- Alan 50 Citra 51 Tomi 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 Citra 51 Tomi 52 rows selected. SQL > commits; Commit complete.
Warning
-
Create a duplicate table in your login so that it can be used as a content reference when no data has been deleted (in case you have any questions).
SQL > create table alan.names_backup as select * from names; Table created.
