Re: Removing duplicates from unique
}From: "Vince Pachiano" <pachiano@dayton.bassinc.com>
}Date: 3 Jun 1997 21:10:13 GMT
}X-Informix-List-Id: <news.38695>
}
}Somehow some duplicate data "sneaked" into my database
}that should have a unique index constraint. Well, I have plugged the
}leaks, but now I am stuck with a database that I cannot re-index
}due to the duplicate data.
}
}How can I easily (AUTOMATED) remove the duplicate data?
}
}I can probably write some UNIX scripts to sort/sort -u, etc., but
}I was hoping there was a better way
I'm going to give you a reprise of an answer I first gave in May 1994. You
safely assume that the midnight timestamp is artificial; I have a script
which cleans out junk from the headings of emails and it sometimes is too
ruthless. The situation described in the message is a little more complex,
probably, than yours, but that just makes your task easier.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
---------------------------------------------------------------------------
Date: Mon, 2 May 94 00:00:00 PST
From: johnl (Jonathan Leffler)
To: brianr@uu5.psi.com
Subject: Re: Unique table entries
On Friday, I wrote:
}Date: Fri Apr 29 08:38:56 1994
}From: johnl (Jonathan Leffler)
}
}>From: brianr@magnus1.com (Bulletin board login)
}>Date: Wed, 27 Apr 1994 14:01:44 GMT
}>X-Informix-List-Id: <news.6534>
}>
}>I was wondering if anyone could help with an ACE and SQL problem. I need
}>to query a equipment table for duplicate fields and fields with partial
}>duplicate data and can't seem to find the right commands.
}
}>I would like to query the equipment table for duplicate serial numbers
}>and then check to see if the model number is > 6 characters and delete
}>the duplicates.
}
}Let's assume the table looks somewhat like:
}
}CREATE TABLE Equipment
}(
} SerialNumber CHAR(15) NOT NULL,
} ModelNumber CHAR(15) NOT NULL
});
}
}I assume that you want to delete just one of the duplicates, not all the
}duplicated entries -- you need to immensely precise when specifying what
}you want to happen.
}
}SELECT SerialNumber
} FROM Equipment
} WHERE LENGTH(ModelNumber) > 6
} GROUP BY SerialNumber
} HAVING COUNT(*) > 1;
}
}This lists the SerialNumbers with duplicate entries. I'm not convinced you
}want the WHERE filter -- and LENGTH may not be available in 2.10.03.
}
}Then to delete all but one of the duplicate entries, you have to be very
}careful, and you test the DELETE by replacing DELETE with 'SELECT *' so
}that you see what you are going to delete first. The code which follows is
}untested.
The SQL that followed had not been tested, and there were a various errors in
it. I have now got properly tested working code. About the only good thing
about Friday's version was that I did say the code was untested...
INSERT INTO Equipment VALUES("AAA11122333", "Wasp Mk II R83");
INSERT INTO Equipment VALUES("AAA11122333", "Wasp Mk II R85");
INSERT INTO Equipment VALUES("AAA11122333", "Wasp Mk II R89");
INSERT INTO Equipment VALUES("AAA11122999", "Wasp Mk II R99");
INSERT INTO Equipment VALUES("AAA11122999", "Wasp Mk II R97");
INSERT INTO Equipment VALUES("AAA11122999", "Wasp Mk II R98");
INSERT INTO Equipment VALUES("AAA11122999", "Wasp Mk II R79");
LOCK TABLE Equipment IN EXCLUSIVE MODE;
SELECT SerialNumber
FROM Equipment
GROUP BY SerialNumber
HAVING COUNT(*) > 1
INTO TEMP x;
SELECT SerialNumber, ROWID xrowid
FROM Equipment
WHERE SerialNumber IN (SELECT * FROM x)
INTO TEMP y;
DELETE FROM Equipment
WHERE SerialNumber IN (SELECT SerialNumber FROM y)
AND ROWID != (SELECT MAX(xrowid)
FROM y
WHERE y.SerialNumber = Equipment.SerialNumber
);
SELECT * FROM Equipment;
UNLOCK TABLE Equipment;
}If you have a transaction log, then you'll do the LOCK inside a
}transaction, and you'll use COMMIT WORK to release the lock instead of
}UNLOCK.
}
}>Then, with the fields that are > 6 chars, I need to clip the field using an
}>UPDATE command if possible.
}
}I take it that "field" is model number? Please be precise when asking
}questions.
}
}UPDATE Equipment
} SET ModelNumber = ModelNumber[1,6];
}
}For models where the number is 6 or fewer characters long, this does
}nothing, but those longer than 6 are truncated.