Thanks for the lead Jeff, maybe you can elaborate? We need to essentially do this:
Insert into orders (number, comment) select max(number) + 1, 'comment' from orders where user = 'user_name'; (Not supported in MySQL) Another user suggested using a select first to get the value, then doing an insert. The only way to insure that nothing occurs between the select and an insert though is a table lock. Unfortunately multiple statements are not supported through the JDBC driver, although it says they are. So, do see how something like this could work using the last_insert_id() to get the max of the number column where a the user column contains a certain value? All help is appreciated. -Allon On 3/19/02 10:14 AM, "Jeff Kilbride" <[EMAIL PROTECTED]> wrote: > UPDATE sequence_table > SET sequence = LAST_INSERT_ID(sequence + 1) > > Further calls to LAST_INSERT_ID on the same connection will return the value > "sequence + 1". It's connection specific, so multiple connections can update > your sequence table without interfering with each other. > > There's a great example of this in Paul DuBois' book "MySQL" from New > Riders. > > --jeff > > ----- Original Message ----- > From: "Allon Bendavid" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Monday, March 18, 2002 7:57 PM > Subject: INSERT ... SELECT not supported? > > >> Hello All- >> >> I just read that an Insert ... Select naming the same table is not > supported >> in MySQL. >> >> What is the alternative? What we need to do is simply insert the max of a >> column + 1 to create essentially a counter in a situation where an >> auto_increment counter would not work. >> >> Anyone? >> >> -Allon >> >> >> -------------------------------------------------------------------------- > -- >> Allon Bendavid Imacination > Software >> [EMAIL PROTECTED] > http://www.imacination.com/ >> 805-650-8153 >> -------------------------------------------------------------------------- > -- >> Visit Imacination and start selling on the Web today with Ch-Ching! >> -------------------------------------------------------------------------- > -- >> >> >> >> >> --------------------------------------------------------------------- >> Before posting, please check: >> http://www.mysql.com/manual.php (the manual) >> http://lists.mysql.com/ (the list archive) >> >> To request this thread, e-mail <[EMAIL PROTECTED]> >> To unsubscribe, e-mail > <[EMAIL PROTECTED]> >> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php >> > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > ---------------------------------------------------------------------------- Allon Bendavid Imacination Software [EMAIL PROTECTED] http://www.imacination.com/ 805-650-8153 ---------------------------------------------------------------------------- Visit Imacination and start selling on the Web today with Ch-Ching! ---------------------------------------------------------------------------- --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
