On Jul 26, 2007, at 5:57 PM, Stuart wrote:
I have a table with a composite PK likeCREATE 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 [EMAIL PROTECTED] 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
