Re: Insert ... Select troubles

[Brent Baisley]

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: <mysql@lists.mysql.com>
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
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]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]