Advertisements

Delete Duplicate Rows in an Oracle Table

How to delete duplicate records in Oracle Table

I would consider that the duplicated columns appear one after the other in the table

If yes; below SQL Query will take you through. You may add or remove additional column conditions in your where clause to satisfy the specific scenario

 

DELETE TABLE A WHERE ROWID >(
select min(rowid) from TABLE b
where a.<table.column1>=b.<table.column1>
and a.<table.column2>=b.<table.column2>)

But if your duplicated columns do not appear in the continuous order; I would recommended you create a temporary table that stores the data in the intended ordered format and the proceed with the above operation. You can then later replace the rows of your original table with rows from the Temporary Table

CREATE TABLE TABLE_TEMP AS SELECT * FROM TABLE ORDER BY <table.column1>

Advertisements

About Sunil Padmanabhan

An Indian based out of Dubai who started traveling from 2011 and pretty much caught the travel bug. Every year presents itself as an opportunity to visit and explore a new place.

Posted on September 30, 2013, in DB, Oracle and tagged , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: