Delete duplicate rows from Oracle

Discussion in 'Oracle' started by shabbir, May 26, 2005.

  1. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    There are times when duplicate rows somehow creep into the table. The best scenario to happen this is when the data is to be imported from some other table or data and the Constraints are removed so that data import successfully.

    Now in Oracle you can delete the duplicate entries by just executing a simple SQL because Oracle stores an index to each row in a table known as ROWID

    Code:
    create table duptest
      ( Id varchar(5),
      nonid varchar(5));
    I create a table with just 2 fields. I dont have any constraint on ID because I would import some data which may be duplicated but after importing I would like to make the ID as PRIMARY KEY

    Insert some date to simulate duplicates
    Code:
    insert into duptest values('1','a');
    Insert them more than once.
    Code:
    insert into duptest values('2','b');
    SQL to delete the duplicate rows
    Code:
    delete from duptest where rowid not in (Select max(rowid) from duptest group by id);
    Explanation :- In the subquery I Select a unique rowid from all the rowid's It can be MAX, MIN any one and the group by clause should include all the UNIQUE columns I desire. Say I want a composite primary key then the group by should be group by id1,id2 ...

    The the subquery returns one record for the dupliacte ID's and I delete all of them that are not in the subquery and that deletes all the dupliacte rows from the database.
     
  2. coderzone

    coderzone Super Moderator

    Joined:
    Jul 25, 2004
    Messages:
    736
    Likes Received:
    38
    Trophy Points:
    28
    Thats good way to delete the rows.
     
  3. lead.smart34

    lead.smart34 New Member

    Joined:
    Feb 14, 2008
    Messages:
    77
    Likes Received:
    0
    Trophy Points:
    0
  4. harminder

    harminder New Member

    Joined:
    Jul 24, 2008
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    0
    a way to delete duplicate rows in oracle table You can also detect and delete duplicate rows using Oracle analytic functions:

    delete from
    customer
    where rowid in
    (select rowid from
    (select
    rowid,
    row_number()
    over
    (partition by custnbr order by custnbr) dup
    from customer)
    where dup > 1);
     
  5. kidas

    kidas Super Moderator

    Joined:
    Nov 25, 2008
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    0
    Home Page:
    http://www.club-oracle.com
    Delete Duplicate Rows using Analytic functions
    Code:
    DELETE FROM TABLENAME
    WHERE ROWID IN (SELECT ROWID FROM (SELECT ROW_NUMBER() OVER (PARTITION BY dup_col ORDER BY dup_col_1) rnk
                                  	   FROM   TABLENAME)
                    WHERE rnk>1);
    
     
  6. Alok_1978

    Alok_1978 New Member

    Joined:
    Apr 27, 2010
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Incase if you want to check for all the columns then try this query:

    delete from emp where rowid not in (select max(rowid) from emp group by id,name,no)

    My emp table had 3 columns id,name,no and you need to group by all the columns in order to check if any column contains different value or not.if yes then that record will not be considered as duplicate and will not be deleted. The above query will delete the record only when all the columns have same value
     
  7. rameshb

    rameshb New Member

    Joined:
    Dec 10, 2010
    Messages:
    35
    Likes Received:
    1
    Trophy Points:
    0
    this is way to different from the way i have studies this is cool
     
  8. TPrice

    TPrice New Member

    Joined:
    Aug 24, 2010
    Messages:
    9
    Likes Received:
    1
    Trophy Points:
    0
    Thank you for sharing this information. This is certainly a much easier way to go about it than the way I have been doing things. I wish I had known about this function a long time ago, it would have saved me a lot of extra work.
     
  9. Tobiasgar

    Tobiasgar New Member

    Joined:
    Aug 29, 2011
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    0
    Home Page:
    http://www.mspy.com
    Usefull invention)) i think it's possible to delete much quicklier:crazy:
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice