RE: Duplicate Rows
Herb
I have used this to clear duplicate rows from the database.
Assume that your unique index is on (colx, coly, colz). Then
SELECT colx,coly,colz,COUNT(*)
FROM tablename
GROUP BY 1,2,3
HAVING COUNT(*) > 1
INTO TEMP T1;
will get all the unique keys having duplicate values into T1.
Thankfully I never got too many, so foreach row in T1, I do:
SELECT MAX(rowid) INTO :MaxRowId
FROM tablename
WHERE colx = :T1.colx
AND coly = :T1.coly
AND colz = :T1.colz;
DELETE FROM tablename
WHERE colx = :T1.colx
AND coly = :T1.coly
AND colz = :T1.colz
AND rowid < :MaxRowId;
If you have a lot of duplicates it may make sense to write a program to do this.
HTH
Sujit Pal
----------
From: Herb Blacker[SMTP:herbb@jcdcrs4.jobcorps.org]
Sent: Tuesday, November 11, 1997 4:35 AM
To: Informix List on WWW
Subject: Duplicate Rows
I've got an old table with all columns allowing nulls (I wasn't here at
the time!) with a unique index on it (how they got away with it all these
years I'll never know). The index got corrupted, and rather than trying
to tbcheck it, I thought it would be faster to drop it and re-create it
(we're talking 12M rows here).
Sure enough, it errors out with something like 'cannot insert row with
duplicate values'.
My question is: is there a quick way to isolate the duplicate rows
(perhaps into a temp table) so that I can get rid of them?
I'm using v5.02 on a RS6000 box.
TIA,
Herb
-------------------------------------------------------------------------
Herb Blacker
Cimarron, Inc.
Database Administrator - DOL Job Corps San Marcos, Texas
------------------------------------------------------------------------