Solved

deleting duplicates and triplicates from table

Posted on 2010-11-23
6
316 Views
Last Modified: 2012-08-14
COLL_DATE        SEQ_NO      LOCATION_ID   NO_OF_DONORS
11/22/2010          1                 LOCN100008       28      
11/22/2010          1                 LOCN100008       28
11/22/2010          1                 LOCN100008       28
11/20/2010          1                 LOCN100008       42
11/20/2010          1                 LOCN100008       42
11/20/2010          1                 LOCN100008       42

This is data for just one location and date range. Like this I have 17 locations. SO I need to remove duplicates. What is the easiest query.
I did a query but its taking time.
 DELETE FROM DS_SUB_CNTR_REBOOKING a
 WHERE a.ROWID >
          ANY (SELECT ROWID
                 FROM DS_SUB_CNTR_REBOOKING b
                WHERE b.seq_no = a.seq_no
                      AND b.location_id = a.location_id
                      and b.coll_date = a.coll_date);
0
Comment
Question by:anumoses
6 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 42 total points
ID: 34199098
please try this:

with data as ( select t.*, row_number() over (partition by seq_no, location_id, coll_date order by rowid) rn
  from DS_SUB_CNTR_REBOOKING t
)
delete data where rn > 1

Open in new window

0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 42 total points
ID: 34199106
Try this alternative as well, it may optimize better

 DELETE FROM DS_SUB_CNTR_REBOOKING
 WHERE ROWID NOT IN (
      SELECT Min(ROWID)
      FROM DS_SUB_CNTR_REBOOKING
    Group by seq_no, location_id, coll_date)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34199111
the query looks fine as is.  If it's taking a long time,  are you sure it's actually doing something and not waiting on a lock from some other session?
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 52

Expert Comment

by:Huseyin KAHRAMAN
ID: 34199173
try this:

delete from mytable where rowid in
(
select r from (
select rowid r, row_number() over (partition by seq_no) rn from mytable
) x where rn>1
)
0
 
LVL 52

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 41 total points
ID: 34199179
I missed the order by clause:

delete from mytable where rowid in
(
select r from (
select rowid r, row_number() over (partition by seq_no order by COLL_DATE) rn from mytable
) x where rn>1
)
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 34229683
thanks
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question