On Jul 26, 2007, at 5:57 PM, Stuart wrote:
I have a table with a composite PK like
CREATE TABLE t (
grp INT NOT NULL,
itm SMALLINT NOT NULL,
...,
PRIMARY KEY (grp,itm));
Normally the app takes care of providing the correct
grp,itm values when inserting records. However
(during a long period of development), I need to
repeatedly reload data into the table from a data
source (a select statement) that has grp values but
no itm values. These itm values need to be small
numbers (1 to COUNT(itm) for each grp value) and
capture the order in which the data was generated
by the select.
MySql seems to have an auto_number function(?) that
takes an optional argument which would be grp in this
case, that (judging from the manual, I don't actually use
MySql) gives the behavior I want (restarts numbering
from 1 when grp value changes).
I'd recommend writing a function in a language that allows you to
store state information between calls, such as plperl and have it
handle the counting, reseting the count every time grp changes. Of
course that means you need to order by grp in your select (and grp
has to be the first sort key). If you can't do that, your next best
bet is to populate itm with a sequence (not resetting) and then
adjust itm after the fact by selecting min(itm) ... group by grp.
Might want to do that in a temp table to avoid bloating the main table.
Note that anything that involves resetting a sequence or anything
like that is going to be a big race condition if you have multiple
inserting processes.
--
Jim Nasby jim@xxxxxxxxx
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
