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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
