Path: utzoo!utgpu!jarvis.csri.toronto.edu!clyde.concordia.ca!uunet!tut.cis.ohio-state.edu!purdue!decwrl!shlump.nac.dec.com!arkham.enet.dec.com!nmeser!may
From: may@28182.dec.com (Patrick May)
Newsgroups: comp.databases
Subject: Re: SQL query for duplicates
Message-ID: <36@arkham.enet.dec.com>
Date: 22 Dec 89 17:16:58 GMT
Sender: news@arkham.enet.dec.com
Organization: Digital Equipment Corporation
Lines: 45


In article <5784@uhccux.uhcc.hawaii.edu>, larned@uhccux.uhcc.hawaii.edu (Paul Larned) writes...
>I am starting an application in Oracle v.5.1b Prof. for MS-DOS for a
>network of PCs which will use RDBMS v.6 for OS/2--this after years of
>writing applications in a dBASE environment.  I am importing a fixed-
>column data file from a UNIFY database into an ORACLE-created table.
>The primary key field I want to use has several duplicates in it from
>an unprotected UNIFY environment.  I am having trouble writing a SQL
>statement to identify the duplicate 4-digit numbers (in a character
>field) in the new database so that they can be changed or deleted.  I
>hope I am not reduced to listing all rows and visually identifying the
>duplicates.  There are about 1000 rows.  I would appreciate any
>suggestions.


     I can think of two ways to remove duplicates from an Oracle database
table.  The first will work with any SQL-based database:

          1.  Create a temporary table with the same structure as the
              one containing the duplicates.

          2.  INSERT INTO new_tab
              SELECT DISTINCT *
              FROM dup_tab;

          3.  Delete all records from dup_tab and refill it from new_tab.

     This works if entire rows are duplicated.  If you just want to find
duplicate values in a particular field, the following should be helpful:

          SELECT DISTINCT col_name, count(*)
          FROM dup_tab
          GROUP BY col_name
          HAVING count(*) > 1;

     This will display all duplicate values for col_name and the number of
times that value appears in dup_tab.

     The second method is Oracle specific.  In every Oracle table there is a
column named ROWID.  The value stored in this column is unique across the table
(and, I believe, across the entire database).  There are some restrictions on
its use (documented in the manuals), but you should be able to use it to specify
the exact row you wish to delete.

Patrick