Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Delete the duplicated ones

Re: Delete the duplicated ones

From: C Chang <cschang_at_maxinter.net>
Date: Thu, 06 Mar 2003 00:08:35 -0500
Message-ID: <3E66D7D3.17D0@maxinter.net>


Mark D Powell wrote:
>
> C Chang <cschang_at_maxinter.net> wrote in message news:<3E658835.45DE_at_maxinter.net>...
> > I have tried with generic standard syntax to delete the duplicated
> > rows. But something wrong here, that I did not delete correct number of
> > duplicated row.
> > example:
> > with the table T( col1 varchar2(5), col2 varchar(2));
> > If I used the query of
> > delete from T t1
> > where t1.rowid <> <-- or use ">"
> > ( select min(t2.rowID) from T t2
> > where t1.col1 = t2.col1
> > and t1.col2 = t2.col2) )
> > for original table T, I got 0 row delete. If I have 5 rows duplicated
> > I should have 5 rows deleted. However, with known 5 duplicated, I got
> > more than 5 rows deleted. This is on my 8.1.6 version on a NT 4 with
> > one 550 MHz CPU. My table has 27 columns, one is an object type, some
> > of the columns may have NULL value. And I did use the reference to
> > compare the t1.referenced.variables of the object type. There was no
> > duplicated row in the original table. Anyone has a good suggestion.
> >
> > C Chang
>
> C, here is SQL to find an delete duplicates; adjust the key line to
> include all necessary columns to identify a duplicated row
> --
> -- delete from
> -- target
> -- where a.condition = X
> -- and a.rowid > (
> -- select min(b.rowid)
> -- from target b
> -- where b.key = a.key
> -- group by b.key
> -- )
>
> HTH -- Mark D Powell --

Thanks all. I am pretty sure that i have tried your method, but it return 0 rows deleted where I am sure there are duplicates. In term of looking to AskTom.oracle.com or google.com, I have looked those places and tried all possible ways from those places. None of works ( I have at least read 17 articles from web). Unless someone wants to run my real data. It is 1.3 M rows.

C Chang Received on Wed Mar 05 2003 - 23:08:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US