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]
