Hi, Actually i update(increment by 1 ) a value in a table with only one row, ie. update table1 set col = col+1; Can i get the updated value without diong select in the same query with update. Because if i run Select after that than some time is lost and in the mean time any other process updates the table and i do not get the exact value. Thanks, Abhishek Jain
On 4/22/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > From 'Section 13.5.3 SET Syntax' of the 5.0.18 ref manual: > > IDENTITY = value > > The variable is a synonym for the LAST_INSERT_ID variable. > It exists for compatibility with other database systems. You > can read its value with SELECT @@IDENTITY, and set it using > SET IDENTITY. > > > INSERT_ID = value > > Set the value to be used by the following INSERT or ALTER > TABLE statement when inserting an AUTO_INCREMENT value. This > is mainly used with the binary log. > > > LAST_INSERT_ID = value > > Set the value to be returned from LAST_INSERT_ID(). This is > stored in the binary log when you use LAST_INSERT_ID() in a > statement that updates a table. Setting this variable does > not update the value returned by the mysql_insert_id() C API > function. > > Eg. > > mysql> select @@last_insert_id; > +------------------+ > | @@last_insert_id | > +------------------+ > | 0 | > +------------------+ > 1 row in set (0.04 sec) > > mysql> select @@insert_id; > +-------------+ > | @@insert_id | > +-------------+ > | 0 | > +-------------+ > 1 row in set (0.00 sec) > > mysql> select @@identity; > +------------+ > | @@identity | > +------------+ > | 0 | > +------------+ > 1 row in set (0.00 sec) > > mysql> set @@last_insert_id = 5; > Query OK, 0 rows affected (0.00 sec) > > mysql> select @@last_insert_id; > +------------------+ > | @@last_insert_id | > +------------------+ > | 5 | > +------------------+ > 1 row in set (0.00 sec) > > mysql> select @@insert_id; > +-------------+ > | @@insert_id | > +-------------+ > | 5 | > +-------------+ > 1 row in set (0.00 sec) > > mysql> select @@identity; > +------------+ > | @@identity | > +------------+ > | 5 | > +------------+ > 1 row in set (0.00 sec) > > So it appears you can use either of the three variables > above to achieve the same effect. > > Regards > > Keith > > On Sat, 22 Apr 2006, Michael Stassen wrote: > > > To: David T. Ashley <[EMAIL PROTECTED]> > > From: Michael Stassen <[EMAIL PROTECTED] > > > Subject: Re: How to Find Most Recent Autoincrement Index Assigned??? > > > > David T. Ashley wrote: > > > I'm using PHP, and I sometimes INSERT new records in a table. MySQL > > > assigns > > > a new autoincrement int field on each INSERT ... nothing surprising > > > there. > > > It goes 1, 2, 3, etc. > > > > > > What query can I use to find out what value this int autoincrement > > > assigned > > > field was? I could of course SELECT based on what was just inserted, > > > but > > > that seems inefficient. > > > > > > Thanks for any help, > > > Dave. > > > > LAST_INSERT_ID() > > > > <http://dev.mysql.com/doc/refman/4.1/en/information-functions.html> > > > > Michael > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
