Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

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);
0
anumoses
Asked:
anumoses
3 Solutions
 
Guy Hengel [angelIII / a3]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
 
cyberkiwiCommented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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
 
HainKurtSr. 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

Featured Post

Upgrade your Question Security!

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

Tackle projects and never again get stuck behind a technical roadblock.
Join Now