Re: Deleting all duplicate rows.

From: InfoAd - Jurrian Beuker <infoad_at_xs4all.nl>
Date: 1996/11/12
Message-ID: <569785$r62_at_news.xs4all.nl>#1/1

gidwani_at_alpha1.csd.uwm.edu (Vinay K Gidwani) wrote:

>I am trying to delete all duplicate rows in a table. The duplicates are to
>be determined on a few keys. The way I am thinking of implementing it
>now is
 

>Insert into temp_table as
>Select key1, key2, key3 from
> table
> group by key1, key2, key3
>.........

You can use :

delete from XXX where rowid in
( select max(rowid)
  from XXX
  group by A,B,C
  having count(*)>1)

A,B,C are de fields withs combinaten determined is you record is uniek. You must repeat de statment until is thus not delete anny more records. A statment with thus this in one run is :

delete from XXX where rowid NOT in
( select max(...
 ...same as above)

Bye Jurrian

---

InfoAd, Buro voor informatica.
Breezand 8, 3891 ZR Zeewolde
Tel (036) 5221108, Fax (036) 5221108
KvK Lelystad nr. 39047240 VRI nr 2096

Ps. Let niet op de taalfouten, ik ben woordblind. Received on Tue Nov 12 1996 - 00:00:00 CET