How to : Get rid of duplicate rows in an oracle sql table
Get rid of duplicate rows in an oracle sql table
If you are using Oracle Database, a common problem is duplicate rows in tables. Usually this happens when unique constraints are removed during loading. Whatever may be the reason, you can remove duplicate records. Duplicate records have identical values for all columns (OR columns that are part of unique key).
Once you've mastered the technique outlined in this article, I recommend you check out the Database Design Resource to learn more about good database normalization techniques.
There is more than one way to delete duplicate records. Let's assume Table_A has the following data:
Col1 Col2
------ -------
101 201
102 202
103 203
102 202
104 204
101 201
Let's look at the first method.
Backup your original table. This is the most important step! Make sure your backup is good and you can restore original table if you need to. Here is the code to delete duplicate records:
DELETE FROM Table_A a
WHERE ROWID != ( SELECT MAX(ROWID)
FROM Table_A b
WHERE b.col1 = a.col1
AND b.col2 = a.col2 );
Oracle has a pseudo column called "ROWID", which is unique for each row in a table. So we can use that to delete duplicate records. The above code will find only one ROWID for non-duplicate records. For duplicate records, it'll delete a record with lower ROWID. If there are more than two columns that make up unique key for a record, then you should include all those columns in WHERE condition. There are two duplicate records in the example; it does not matter which record we delete since all columns have the same values.
Verify your results and commit your work.
Now let's look at a second method.
Backup your original table. This is still the most important step! Make sure your backup is good and you can restore original table if you need to. Create a temporary table with unique records using DISTINCT clause. Delete all records from original table. Insert all records from temporary table into original table. Verify your results and commit your work.
Depending on your situation, you can choose one of the two methods for faster results. Duplicate records can be prevented using Unique Indexes. Unique Indexes guarantees that columns within the index have no duplicate values.