Re: Suggestions for Detecting/Eliminating Duplicate Records

From: Michael J. Ort <michael_ort_at_my-deja.com>
Date: 2000/04/10
Message-ID: <8ct6tb$gc9$1_at_nnrp1.deja.com>#1/1


Don't know the specifics of what you're looking for, but you can use:

  1. To detect duplicates: SELECT COUNT(any_NN_column) , dup_column_1 , dup_column_2 : : FROM table_with_dups GROUP BY dup_column_1 , dup_column_2 : : HAVING COUNT(any_NN_column)>1;
  2. To delete duplicates (remember ROWID will always be unique): DELETE FROM table_with_dups A WHERE A.ROWID > (SELECT MIN(B.ROWID) FROM table_with_dups B WHERE A.dup_column_1 = B.dup_column_1 AND A.dup_column_2 = B.dup_column_2 : : );
  3. To prevent duplicates: ALTER TABLE table_with_dups ADD CONSTRAINT UK_dup_column_1_dup_column_2_... UNIQUE (dup_column_1,dup_column_2,...);

Hope one or more of those fits your situation.

Michael J. Ort

In article <38F1F092.A6774816_at_uscreative.com>,   "Michael C. Wilkerson" <Mike.Wilkerson_at_uscreative.com> wrote:
> Was wondering if anyone could recommend a good source
> (book/website/whatever) that describes methods for the detection and
> elimination of duplicate records. I'm very familiar with Group By, I
> need something a little more advanced than that. Something that
> specifically addresses Oracle and it's set of functions would be best,
> but I'd be interested in anything that has good recommendations that I
> could convert to Oracle.
>
> Thanks,
> Michael
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Apr 10 2000 - 00:00:00 CEST

Original text of this message