Re: Unique table entries
>From: brianr@magnus1.com (Bulletin board login)
>Subject: Unique table entries
>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
>date and can't seem to find the right commands.
^
data?
>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 (which is
incredibly old!).
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.
SELECT SerialNumber
FROM Equipment
GROUP BY SerialNumber
HAVING COUNT(*) > 1
INTO TEMP x;
DELETE FROM Equipment E1
WHERE SerialNumber IN (SELECT * FROM x)
AND ROWID != (SELECT MAX(ROWID)
FROM Equipment E2
WHERE E2.SerialNumber - E1.SerialNumber
);
This may not work -- I'm not sure that you can refer to Equipment in that
sub-query. If not, then you need to do:
LOCK TABLE Equipment IN EXCLUSIVE MODE;
SELECT SerialNumber, ROWID xrowid
FROM Equipment
WHERE SerialNumber IN (SELECT * FROM x)
INTO TEMP y;
DELETE FROM Equipment E1
WHERE SerialNumber IN (SELECT xrowid FROM y)
AND ROWID != (SELECT MAX(xrowid)
FROM y
WHERE y.SerialNumber - E1.SerialNumber
);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.
>I actually just wanted to print a copy of what I would be deleting (using ACE)
>and then run the SQL script.
>
>I am new to Informix SQL and really appreciate any help,
>
>P.S. I am using an old version of Informix - 2.10
Is that 2.10.00 or 2.10.03? Not that it really matters, but 2.10.00 is
about 7 years old, and 2.10.03 must be about 5 years old. An upgrade might
be in order, not least because the later manual are much better than the
older ones.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>