Re: delete 2 of the 3 triplicates from a table

delete from t1
 where rowid IN ( select rowid1

                         from (select rowid rowid1, row_number() over
(partition by id order by rowid) rown
                                 from t1 )
                   where rown != 1
                    );

Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance, RAC and EBS
Blog: http://orainternals.wordpress.com/ Oracle ACE Director and OakTable member <http://www.oaktable.com/>

Co-author of the books: Expert Oracle
Practices<http://tinyurl.com/book-expert-oracle-practices/> , Pro Oracle SQL, <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert
RAC Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices <http://tinyurl.com/book-expert-plsql-practices>

<http://tinyurl.com/book-expert-plsql-practices>

On Fri, Feb 7, 2014 at 8:31 AM, Lyall Barbour <lyallbarbour_at_sanfranmail.com>wrote:

> Hi,
>
> I need to delete 2 of the 3 triplicates i found in a table. Anyone have
> an easy way to do that?
>
>
>
> Oracle 10.2.0.2 table has two columns, the ID that has triplicates in it
> and a date column (sysdate of when info went into table)
>
>
>
> I found the triplicates with
>
>
>
> select <id column> from <table>
>
> group by <id column>
>
> having (count(<id column) > 1); -- then sub sequently, > 2 (> 3
> returned no rows)
>
>
>
> Thanks
>
> Lyall Barbour
>

--
http://www.freelists.org/webpage/oracle-l