Re: deleting duplicate rows

In article <1994Jul29.042203.59417_at_frodo.cc.flinders.edu.au>

           carr_at_ist.flinders.edu.au "Matthew J Carr" writes:

> 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????
>
>
>

I have not tested this, but how about-

    DELETE FROM th_table t1
    WHERE rowid !=

      (SELECT MIN(rowid)
       FROM th_table t2
       WHERE t2.thread_id = t1.thread_id
       AND   t2.mess_id = t1.mess_id
       AND   t2.rowid != t1.rowid
      )

-- 
Stephen Lappin

+-----------------+
| +-------------+ |  Real Time Engineering Ltd.
| |  Real Time  | |  Capital House
| +-------------+ |  20 Park Circus
| Engineering Ltd |  Glasgow G3 6BE         Tel: +44 (0)41 332 9400
+-----------------+  Scotland U.K.          Fax: +44 (0)41 331 2509