Re: SQL - Delete duplicated rows

From: Roy Johnson <rjohnson_at_shell.com>
Date: Tue, 13 Jul 1993 21:21:56 GMT
Message-ID: <RJOHNSON.93Jul13152156_at_conus.shell.com>


pascal_at_BComeau.Hydro.Qc.CA (Pascal Pitre) writes:

> I want to insert the values a 9000 lines table that have no index,
> into a new table with index. Unfortunatly, i realized that my table
> have a few rows identical, or at least the tree columns that compose
> the index are identical. Do you have an idea to delete, or just
> to find out wich rows are duplicated ???
 

> I am using 6.0.33 on SUN 670MP.
 

> The 1st table is :

[Just like the second, but without the NOT NULLs]

> The second table is :
 

> Name Null? Type
> ------------------------------- -------- ----
> CODE_OUVRAGE NOT NULL CHAR(10)
> NO_DEVERSOIR NOT NULL CHAR(15)
> DATE_MESURE NOT NULL DATE
> HEURE_MESURE NOT NULL NUMBER(2)
> CODE_REMARQUE NUMBER(2)
> DEBIT_DEVERSOIR NUMBER(5,2)
> INSPECTEUR CHAR(9)
 
> The NOT NULL columns make the index.

If rows that duplicate the index columns also duplicate the other columns (ie, the rows are actually identical), you can simply do

INSERT INTO table2
  SELECT DISTINCT * FROM table1;

Otherwise you will have to do something like this:

INSERT INTO table2
  SELECT * FROM table1
  WHERE rowid IN (
    SELECT MIN(rowid) FROM table1
    GROUP BY CODE_OUVRAGE, NO_DEVERSOIR, DATE_MESURE, HEURE_MESURE   );     

--
-------- Roy Johnson ---- rjohnson_at_shell.com ---- Speaking for myself --------
"When the only tool you have is Perl, the whole | "Hooray for snakes!"
 world begins to look like your oyster." -- Me  |  -- The Simpsons (29 Apr 93)
Received on Tue Jul 13 1993 - 23:21:56 CEST

Original text of this message