Thursday, 25 November 2010

Oracle PL/SQL remove duplicates from a table

 If you have an oracle database table with duplicates in, and you want to remove said duplicates then you can use something like this:

DELETE FROM myTable
WHERE  ROWID IN (SELECT LEAD(ROWID) OVER(PARTITION BY myColumn ORDER BY NULLFROM myTable);

Replace myTable with the table name, and myColomn with the column that contains the duplicates.

There are other ways to do this, please feel free to list alternatives in the comments.

Hope this helps.

1 comment:

  1. There are lots of information about oracle have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get to the next level in oracle. Thanks for sharing this.

    Oracle Training Institutes in Chennai
    Best Oracle Training in Chennai

    ReplyDelete