This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Nov 20, 2012 6:53 PM by vansul Go to original post RSS
  • 15. Re: Deleting all duplicate rows from a table
    user10857924 Journeyer
    Currently Being Moderated
    Hi Ranit,

    id is the key column and you see there are multiple records with same id in sample data and base on what you suggest it will remove all record where as we need to keep 1 record per id.
    Also in my sample data i mimic rowid as incremental no.

    please correct me if i miss something.

    Thank you
  • 16. Re: Deleting all duplicate rows from a table
    xtender Pro
    Currently Being Moderated
    No, not all queries could cause this problem.

    Just little example:
    Your query:
    -- Test table:
    SESS1> create table t_dups as select 1 n from dual connect by level<=3;
    
    Table created.
    
    SESS1> select rowid,n from t_dups;
    
    ROWID                       N
    ------------------ ----------
    AAAb3OAAJAAAfs7AAA          1
    AAAb3OAAJAAAfs7AAB          1
    AAAb3OAAJAAAfs7AAC          1
    
    3 rows selected.
    
    SESS1> -- now updating last row:
    SESS1> update t_dups set n=2 where rowid='AAAb3OAAJAAAfs7AAC';
    
    1 row updated.
    
    SESS1> -- now we have:
    SESS1> select rowid,n from t_dups;
    
    ROWID                       N
    ------------------ ----------
    AAAb3OAAJAAAfs7AAA          1
    AAAb3OAAJAAAfs7AAB          1
    AAAb3OAAJAAAfs7AAC          2
    
    3 rows selected.
    ---------------------------------------------------------------------------------------------------------------
                                                      SESS2> -- in another session:
                                                      SESS2> delete from t_dups d1 where d1.rowid in (
                                                        2    select rid from (
                                                        3      SELECT d2.rowid rid, row_number() over(
                                                        4        partition by d2.n order by null
                                                        5      ) rn
                                                        6      FROM t_dups d2
                                                        7    )
                                                        8    where rn > 1
                                                        9  );
                                                      -- waiting for unlock...
    ---------------------------------------------------------------------------------------------------------------
    SESS1> commit;
    
    Commit complete.
    ---------------------------------------------------------------------------------------------------------------
                                                      -- unlocked:
                                                      2 rows deleted.
                                                      
                                                      SESS2> commit;
    
                                                      Commit complete.
    
                                                      SESS2> select * from t_dups;
    
                                                               N
                                                      ----------
                                                               1
    
                                                      1 row selected.
    ---------------------------------------------------------------------------------------------------------------
    And now another one:
    SESS1> drop table t_dups purge;
    
    Table dropped.
    
    SESS1> create table t_dups as select 1 n from dual connect by level<=3;
    
    Table created.
    
    SESS1> select rowid,n from t_dups;
    
    ROWID                       N
    ------------------ ----------
    AAAb3PAAJAAAfs7AAA          1
    AAAb3PAAJAAAfs7AAB          1
    AAAb3PAAJAAAfs7AAC          1
    
    3 rows selected.
    
    SESS1> -- now updating last row:
    SESS1> update t_dups set n=2 where rowid='AAAb3PAAJAAAfs7AAC';
    
    1 row updated.
    
    SESS1> select rowid,n from t_dups;
    
    ROWID                       N
    ------------------ ----------
    AAAb3PAAJAAAfs7AAA          1
    AAAb3PAAJAAAfs7AAB          1
    AAAb3PAAJAAAfs7AAC          2
    
    3 rows selected.
    
    ---------------------------------------------------------------------------------------------------------------
                                                      SESS2> -- in another session:
                                                      SESS2> delete from t_dups d1
                                                        2  where
                                                        3    d1.rowid>( select min(d2.rowid)
                                                        4               from t_dups d2
                                                        5               where d1.n=d2.n
                                                        6              );
                                                      -- waiting for unlock...
    ---------------------------------------------------------------------------------------------------------------
    SESS1> -- unlock:
    SESS1> commit;
    
    Commit complete.
    
    ---------------------------------------------------------------------------------------------------------------
                                                      -- unlocked:
                                                      1 row deleted.
    
                                                      SESS2> select * from t_dups;
    
                                                               N
                                                      ----------
                                                               1
                                                               2
    
                                                      2 rows selected.
    Regards,
    Sayan Malakshinov
    http://orasql.org
  • 17. Re: Deleting all duplicate rows from a table
    Stew Ashton Expert
    Currently Being Moderated
    Thank you very much for that illustration. I think I get the point now: the correlated subquery guarantees write consistency by referring to D1.N, so that value has to be rechecked when the "current get" is done. My subquery is independent, so no rechecking is done.

    Now suppose N could be null: in that case I would suggest
    DELETE FROM T_DUPS D1
    WHERE D1.ROWID > (
      select min(d2.rowid)
      FROM T_DUPS D2
      WHERE decode(D1.N,D2.N,0,1) = 0
    );
  • 18. Re: Deleting all duplicate rows from a table
    vansul Pro
    Currently Being Moderated
    delete from <tablename>
    where rowid in (
    select max(rowid)
    from <tablename>
    where <yourConditions if any>
    group by <group which is duplicate>
    having count(*) >1
    )

    it will delete if there are 2 rows then recent one will be deleted
    if 3 or more then only one row will be deleted
    you can re run the above query till you found 0 rows deleted.
    if there are 10 rows duplicate of one row then you need to run 9 times.
    there will be no single rows deleted in this query.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points