Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

deleting duplicates and triplicates from table

Posted on 2010-11-23
6
Medium Priority
?
320 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 168 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 168 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

Expert Comment

by:HainKurt
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 60

Assisted Solution

by:HainKurt
HainKurt earned 164 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

618 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