MySQL Forums
Forum List  »  Newbie

Re: Select and update at the same time (with selected value)? LAST_INSERT_ID
Posted by: Rick James
Date: June 30, 2010 11:14PM

See if this works for you:
UPDATE tbl SET
        server = 'serverA',      -- Grab the row for yourself
        id = LAST_INSERT_ID(id)  -- to get LAST_INSERT_ID() set
    WHERE server IS NULL         -- make sure row is available
    ORDER BY ts                  -- make the selection deterministic
    LIMIT 1;                     -- grab only one row
SELECT LAST_INSERT_ID();     -- find out which row you grabbed
Note: In the gap between the UPDATE and the SELECT, another thread may run its copy of the UPDATE statement. This is not a problem. LAST_INSERT_ID is thread-specific.

There no need for locking, either in MyISAM or InnoDB.

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.