Thursday, 9 June 2016

Remove duplicate rows from table in Oracle

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);

column 1 and column2 as I mentioned for candidate keys. You can very well add all columns in it.

Related Links

No comments:

Post a Comment

t> UA-39527780-1 back to top