RE: remove duplicates
- From: Upendra N <nupendra@xxxxxxxxxxx>
- To: <barb.baker@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 28 Jul 2010 13:28:49 -0400
You create create another table with unique records:
create table new_table
as select WOE_SID, WO_SID, INVC_AMT from woe_fact_barb group by WOE_SID,
WO_SID, INVC_AMT;
rename woe_fact_barb to old_table;
rename new_table to woe_fact_barb;
Remember to create all integrity constraints, indexes etc.
If you can't create a new table you could follow the procedure below to delete
the duplicates:
http://www.devx.com/tips/Tip/14665
Either way, the procedure may take a while - depending on the size of the table
and how busy your database is.
HTH
-Upendra
Date: Wed, 28 Jul 2010 11:14:25 -0600
Subject: remove duplicates
From: barb.baker@xxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
oracle 9.2.0.7 on linux
I have some duplicated records in a table (woe_fact_barb)
I cannot come up with the sql to remove these. Can anyone help?
The only way to identify the duplicate is that there are 2 records with the
same WO_SID.
WOE_SID WO_SID INVC_AMT---------- ---------- ----------
1079559 177694790 7898.1 1080085 177694790 7898.1
1079589 177694810 24026.19 1079663 177694810 24026.19
1079567 177694858 17088.35 1080135 177694858 17088.35
I've tried various combinations of sql sorta like this:
sql>delete from woe_fact_barb
2 (select min(woe_sid)
3 from woe_fact_barb )
4 where wo_sid=177694790
5 /
Nothing I've tried is close.
thanks for any help!
Barb
_________________________________________________________________
The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with
Hotmail.
http://www.windowslive.com/campaign/thenewbusy?tile=multicalendar&ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5
Other related posts:
