deleting duplicates and triplicates from table

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);
LVL 6
anumosesAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
cyberkiwiConnect With a Mentor Commented:
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
 
sdstuberCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
HainKurtSr. System AnalystCommented:
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
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
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
 
anumosesAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.