Re: Q: Oracle sqlplus way to capture all duplications and removal.

From: Turkbear <john.greco_at_dot.state.mn.us>
Date: Fri, 05 Sep 2003 08:51:11 -0500
Message-ID: <525hlv0na2ce135tovoc328c41q4nvsi65@4ax.com>

colocoloc_at_yahoo.com (ColoC) wrote:

>Hi, is there someone to help me?
>
>I have got a data duplication case in a big Oracle 8 table. Many of
>the records were processed and loaded twice, meanwhile there are much
>more good records without duplication. The table has 60 fields for
>each record.
>
>Is there a simple Oracle sqlplus way to capture all these duplications
>and remove the duplications?
>
>Thanks.
>
>ColoC

Here is one method that was posted to this group some time ago..

---

Via rowid. Approach can differ based on what records you want to preserve, but the idea is

delete from your_table where rowid in (
  select rowid
  from your_table x
  where x.rowid > (
    select min(y.rowid)
    from your_table y
    where y.uk_column1 = x.uk_column1
    and y.uk_column2 = x.uk_column2
    and ...)
  )

---

You can also try a UNION with itself --That eliminates duplicates.. Received on Fri Sep 05 2003 - 08:51:11 CDT