Re: deleting duplicate rows

From: Kim Ng <kimmng_at_uswnvg.com>
Date: 29 Jul 94 21:26:08 GMT
Message-ID: <33137_at_uswnvg.uswnvg.com>

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.) Received on Fri Jul 29 1994 - 23:26:08 CEST