Re: deleting duplicate rows

Kim Ng (kimmng_at_uswnvg.com) wrote:
: Matthew J Carr (carr_at_ist.flinders.edu.au) wrote:
: : I have a table consisting of 3 columns: thread_id, mess_id, length
: : I am trying to remove duplicate rows of thread_id and mess_id so only one copy remains
 

: : Is it possible to construct a query using the rowid?
 

: : e.g delete from th_table t1
: : where rowid > (select rowid from th_table
: : where thread_id = t1.thread_id
: : and mess_id = t1.mess_id
: : and rowid < t1.rowid)
 

: : It returns an errror: ORA-01427: single-row subquery returns more than one row
 

: : Can it be done this way????
: :
 

: Try this:
 

: delete from th_table t1
: where rowid > (select min (rowid) from th_table
: where thread_id = t1.thread_id
: and mess_id = t1.mess_id)
 

: This might be faster.
 

: -----------------------------
: Kim Ng
: (Just a low life contract programmer. Thus, my clients won't adopt my views.)

Hi,

Try this new approach !!!

  delete from th_table t1
  where exists ( select 1 from th_table t2

                 where t1.thread_id=t2.thread_id
                 and t1.mess_id=t2.mess_id
                 and t1.rowid < t2.rowid );

be wary of potential null columns !!!

Amit

--
                                  _____________
    Amit Gupta                        churchill        Tel: +44 81 313 5688
    Sr. Database Administrator    =============        Fax: +44 81 313 5699
    Churchill Technology Ltd.
    15-17 London Rd, Bromley BR1 1DE, UK.    Email: amit_at_churchill.co.uk