Re: Insert ... Select troubles
Thanks for the Brent, What do you think about trying to make this work by using a stored procedure? A colleague mentioned it to me but I can't seem to get my head wrapped around it yet. - Thanks >>> "Brent Baisley" <[EMAIL PROTECTED]> 1/15/07 7:45 AM >>> When you said "multiple field unique key", I assumed those two field were your primary key. The way I described in the easiest way to implement it. Especially since you can do future insert/select without having to worry about figureing out sequence numbers for each group. You may want to think if you need two unique indexes. Obviously if you already have related information in place based on the RecID value it would be difficult to switch. You would need to store the value of both fields to setup a relation. I don't think what you want to do can be done in a single query while also being able to add data in a similar manner in the future. Future insert/selects would require first determining the highest number for each group, then incrementing from there. You could create a "middle" table in structure I described and then run two insert/selects. The first to generate you sequence, then second to populate the main table. - Original Message - From: "Ed Reed" <[EMAIL PROTECTED]> To: Sent: Friday, January 12, 2007 8:22 PM Subject: Re: Insert ... Select troubles 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 11 12 21 22 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: 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 |
Re: Insert ... Select troubles
When you said "multiple field unique key", I assumed those two field were your primary key. The way I described in the easiest way to implement it. Especially since you can do future insert/select without having to worry about figureing out sequence numbers for each group. You may want to think if you need two unique indexes. Obviously if you already have related information in place based on the RecID value it would be difficult to switch. You would need to store the value of both fields to setup a relation. I don't think what you want to do can be done in a single query while also being able to add data in a similar manner in the future. Future insert/selects would require first determining the highest number for each group, then incrementing from there. You could create a "middle" table in structure I described and then run two insert/selects. The first to generate you sequence, then second to populate the main table. - Original Message - From: "Ed Reed" <[EMAIL PROTECTED]> To: Sent: Friday, January 12, 2007 8:22 PM Subject: Re: Insert ... Select troubles 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 11 12 21 22 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: 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 | |
Re: Insert ... Select troubles
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 11 12 21 22 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: 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] >
Re: Insert ... Select troubles
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 11 12 21 22 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: 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
Insert ... Select troubles
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? Thanks
