Solved

deleting duplicates and triplicates from table

Posted on 2010-11-23
6
317 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 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 55

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 55

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

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