Re: delete 2 of the 3 triplicates from a table

From: William Robertson <william_at_williamrobertson.net>
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-l
Received on Sat Feb 08 2014 - 12:07:46 CET

Original text of this message