Thanks for the suggestion Brent. The auto_increment won't work in my situation though. My t1 table has a RecID field that already is my primary key and set to auto_increment. Is there another way that this can be done? Thanks
>>> "Brent Baisley" <[EMAIL PROTECTED]> 1/12/07 1:10 PM >>> Sine ItemCount is part of your unique key, you should just make ItemCount an auto_increment field. Mysql will then handle providing sequential numbering within the group. For example: CREATE TABLE t1 ( GroupCount int, ItemCount int auto_increment, PRIMARY KEY (GroupCount,ItemCount) ) When you do an insert, leave out the ItemCount. insert into t1 (GroupCount) values (1),(1),(2),(2) The t1 table then looks like this; GroupCount ItemCount 1 1 1 2 2 1 2 2 The compound primary key causes a difference auto increment sequence to be made for each record "group". ----- Original Message ----- From: "Ed Reed" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Friday, January 12, 2007 12:42 PM Subject: Re: Insert ... Select troubles > ItemCount is essentially a counter of the records from the select > statement. So, every new INSERT ... SELECT statement gets a new > GroupCount (the next number up) and ItemCount represents the ID of the > items in that new group. > > Does that make sense? > > - Thanks > >>>> "Michael Dykman" <[EMAIL PROTECTED]> 1/12/07 8:13 AM >>> > On 1/11/07, Ed Reed <[EMAIL PROTECTED]> wrote: >> >> I need some help creating an INSERT * SELECT statement that supplies > a record counter in the returned items. Here's the task, >> >> I have t1 and t2. t1 is GroupCount, ItemCount, Field1, Field2, > Field3. t2 is Field1, Field2, Field3. >> >> t1 has a multiple field unique key called Request that has GroupCount > and ItemCount. I would like to create an INSERT * SELECT statement that > takes the three fields from t2 and puts them into t1 and at the same > time automatically fills the GroupCount and ItemCount field. My data in > t1 should look like this. >> >> > +----------+-----------+--------------+--------------+--------------+ >> |GroupCount| ItemCount | Field1 | Field2 | Field3 > | >> > +----------+-----------+--------------+--------------+--------------+ >> | 1 | 1 | data from t2 | data from t2 | data from t2 > | >> | 1 | 2 | data from t2 | data from t2 | data from t2 > | >> | 1 | 3 | data from t2 | data from t2 | data from t2 > | >> | 2 | 1 | data from t2 | data from t2 | data from t2 > | >> | 2 | 2 | data from t2 | data from t2 | data from t2 > | >> | 3 | 1 | data from t2 | data from t2 | data from t2 > | >> | 3 | 2 | data from t2 | data from t2 | data from t2 > | >> | 3 | 3 | data from t2 | data from t2 | data from t2 > | >> | 3 | 4 | data from t2 | data from t2 | data from t2 > | >> | 3 | 5 | data from t2 | data from t2 | data from t2 > | >> | 3 | 6 | data from t2 | data from t2 | data from t2 > | >> | 4 | 1 | data from t2 | data from t2 | data from t2 > | >> | 4 | 2 | data from t2 | data from t2 | data from t2 > | >> | 4 | 3 | data from t2 | data from t2 | data from t2 > | >> | 4 | 4 | data from t2 | data from t2 | data from t2 > | >> | 4 | 5 | data from t2 | data from t2 | data from t2 > | >> | 4 | 6 | data from t2 | data from t2 | data from t2 > | >> | 4 | 7 | data from t2 | data from t2 | data from t2 > | >> | 5 | 1 | data from t2 | data from t2 | data from t2 > | >> | 5 | 2 | data from t2 | data from t2 | data from t2 > | >> | 6 | 1 | data from t2 | data from t2 | data from t2 > | >> | 6 | 2 | data from t2 | data from t2 | data from t2 > | >> | 6 | 3 | data from t2 | data from t2 | data from t2 > | >> > +----------+-----------+--------------+--------------+--------------+ >> >> I can figure most of it out with the exception of the ItemCount > value. What I have so far is this, >> >> INSERT INTO t1 (GroupCount, ItemCount, Field1, Field2, Field3) SELECT > (SELECT Max(GroupCount)+1 From t1), ?????, Field1, Field2, Field3 FROM > t2; >> >> The part with the ????? is what I can't figure out. >> >> Any ideas? >> > > I'm not very clear what ItemCount is supposed to represent.. if you > restate it, perhaps I can help > > -- > - michael dykman > - [EMAIL PROTECTED] >
