Re: Unique table entries
On Friday, I wrote:
}Date: Fri Apr 29 08:38:56 1994
}From: johnl (Jonathan Leffler)
}To: brianr@magnus1.com, informix-list@rmy.emory.edu
}Subject: 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
}>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.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>