Re: delete 2 of the 3 triplicates from a table
- From: William Robertson <william@xxxxxxxxxxxxxxxxxxxx>
- To: oracle-l digest users <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 8 Feb 2014 11:07:46 +0000
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@xxxxxxxxx>
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@xxxxxxxxxxxxxxx>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
>
Other related posts:
