Re: Delete doubles

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Thu, 6 May 1999 10:55:47 +0200
Message-ID: <7grlhl$6e2$1@hermes.is.co.za>

Jérôme Texier wrote in message <7grg19$ev1$1_at_jaydee.iway.fr>...
>
>I have load a table 2 times (there is no Unique key constraint). How can I
>delete my second load without delete my first load ?

SELECT
  rowid,
  id
FROM dups

ROWID ID

------------------ ----------
00001258.0000.0001 1
00001258.0001.0001 2
00001258.0002.0001 3
00001258.0003.0001 1

00001258.0004.0001 2
00001258.0005.0001 3

SELECT

  a.rowid "DELETE THIS",
  b.rowid "KEEP THIS",
  a.id

FROM dups a, dups b
WHERE a.id = b.id
AND a.rowid > b.rowid

DELETE THIS KEEP THIS ID

------------------ ------------------ ----------
00001258.0003.0001 00001258.0000.0001 1

00001258.0004.0001 00001258.0001.0001 2 00001258.0005.0001 00001258.0002.0001 3

DELETE FROM dups
WHERE rowid IN
( SELECT a.rowid FROM dups a, dups b
  WHERE a.id = b.id
  AND a.rowid > b.rowid
)

SELECT
  rowid,
  id
FROM dups

ROWID ID

------------------ ----------
00001258.0000.0001 1

00001258.0001.0001 2
00001258.0002.0001 3

This should work for you I think..

regards,
Billy Received on Thu May 06 1999 - 03:55:47 CDT