Re: Delete the duplicated ones

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