Re: delete 2 of the 3 triplicates from a table
Date: Sat, 8 Feb 2014 11:07:46 +0000
Message-ID: <-2322943018267668005_at_unknownmsgid>
I normally use a variation:
delete tablename
where rowid in
( select lag(rowid) over (partition by id order by someorder) from
tablename );
where "someorder" is the list of columns to order by such that you retain the "last" member of each duplicate set (if there's no particular ordering you can use "order by null" to satisfy the syntax). This deletes every "previous" row, leaving only the last.
William Robertson
On 7 Feb 2014, at 16:48, Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com> wrote:
Use analytic function (syntax errors expected)
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
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-lReceived on Sat Feb 08 2014 - 12:07:46 CET