Saturday, July 27, 2013

Oracle Tip : Removing all the duplicates from a table

Problem : Problem of Duplicates in a table

Solution : Using Analytics is the best solution. For this problem I have used ROW_NUMBER()


Steps :

  • Create a backup of the table for which you are removing the duplicates 
  • Identify the no. of duplicates
  • Run the statement given below
    • TABLE1 - replace with your table
    • FIELD1,FIELD2 - Group By fields from your table 


    DELETE FROM TABLE1
  1.       WHERE ROWID IN (
  2.                SELECT row_id
  3.                  FROM (SELECT ROWID row_id,
  4.                               ROW_NUMBER () OVER (PARTITION BY FIELD1,FIELD2 ORDER BY ROWID) ROWNO
  5.                          FROM TABLE1)
  6.                 WHERE ROWNO<> 1);
  7.                 

Digg Google Bookmarks reddit Mixx StumbleUpon Technorati Yahoo! Buzz DesignFloat Delicious BlinkList Furl

0 comments : on " Oracle Tip : Removing all the duplicates from a table "