Read this: http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html
and post the SHOW CREATE TABLE for your projects table if that didn't answer your question. Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Dan Tappin" <[EMAIL PROTECTED]> wrote on 09/21/2004 03:04:52 PM: > I am trying to create a table (projects) with the following basic > column structure: > > id (primary key) > id_client > id_clientkey > > The ideas is that I will fill it with data like this: > > table: projects > > id id_client id_clientkey > --------------------------------- > 1 1 1 > 2 1 2 > 3 1 3 > 4 2 1 > 5 1 4 > 6 2 2 > 7 3 1 > > etc. where id_owner is the primary key of a 'client' table with > associated data and the id_clientkey column auto-increments but on > that clients projects. > > What I am going for is when I perform: > > INSERT INTO projects SET id_client = 1 > > the next id_clientkey value would be 5 in this case. > > Ideally my command wound be: > > INSERT INTO projects (id_client, id_clientkey) SELECT 1, > MAX(id_clientkey) + 1 FROM projects WHERE id_client = 1 > > but MySQL results in an error when I run this. I am guessing that > you can use INSERT ... SELECT on the same table for each > argument. > > I am running this via a PHP front end so I figure I have 2 work > arounds: look-up the MAX(id_clientkey) value first then use in a > subsequent query but risk a new row created by another user between > the 2 transactions OR use a table lock but then risk locking out > other users. > > Side Question: If I go down the lock path (which seems the best) > can have other INSERT requests wait until the table is unlocked or > will a INSERT request during a lock simply return an error? There > will be multiple web users triggering INSERT and UPDATE requests > on this table and the extra millisecond of delay waiting inline for > the unlock is not a concern to me. > > I am hoping that there is a more elegant solution than above. I > have read through the manual and a few list and Google searches > with no avail. > > Dan T > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
