Re: better delete statement to remove duplicate rows from exception

Hi,

You can do this:

delete
from orders o1

where rowid  <  (select      max(rowid)
                           from      orders o2
                           where     o1.order_id = o2.order_id
                           group   by  order_id
                           having   count(order_id) > 1);

Obviously you cand firstly do a select statment (on a limited set of data), instead of a delete statement, just to be sure it works as expected.

Best regards
Dias Costa

Jacques Kilchoer wrote:

>In the situation below, is there a better way to write the delete statement that eliminates duplicates? (assuming duplicate rows form at most 5 % of the table rows) Notice that the exceptions table is not analyzed.
>
>If I analyze the exceptions table, is there then another better way to write it?
>
>create table my_exceptions
> (row_id urowid,
> owner varchar2 (30),
> table_name varchar2 (30),
> constraint varchar2 (30)
> );
>
>create table orders
> (order_id number (8) not null,
> order_date date,
> constraint orders_uq1 unique (order_id) disable
> ) ;
>/* -- load table orders with millions of rows */
>create index orders_idx1
> on orders (order_id) ;
>analyze table orders estimate statistics sample 10 percent ;
>alter table orders
> enable constraint orders_uq1
> exceptions into my_exceptions ;
>delete
> from orders a
> where
> a.rowid in
> (select d.delete_row_id
> from
> (select
> min (b.row_id) over (partition by c.order_id) as keep_row_id,
> b.row_id as delete_row_id
> from my_exceptions b, orders c
> where c.rowid = b.row_id
> group by c.order_id, b.row_id
> ) d
> where
> d.delete_row_id != d.keep_row_id
> ) ;
>commit ;
>alter table orders
> enable constraint orders_uq1 ;
>truncate table my_exceptions ;
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Dias Costa
  INET: dcosta_at_lnec.pt

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).