Hi Andy, On Thu, 2002-06-13 at 05:02, Andy Sy wrote: > I want to use an auto-incrementing value that works > across multiple tables. For example I have 3 tables > forumA, forumB, forumC, each of which has a primary > key field called 'msgnum'. However, if I declare > 'msgnum' auto_increment, 'msgnum' will not be > unique across the 3 tables. > > Thus, instead of using an auto_increment column, I > made an auxiliary table which will hold the last used > 'msgnum' and update it each time a new record is > inserted in any of the 3 tables. Since I plan to use > this auxiliary table to hold other values as well and > INSERTs to the 3 tables may happen extremely often, I > would rather not have the overhead of repeatedly LOCKing > and UNLOCKing the table. > > In connection with this, the following 2 issues crop > up: > > #1) Is the following statement guaranteed atomic? > > UPDATE TBL SET COL=COL+1
Yes. > and is there anyway to retrieve the value of COL > that was last set by the connection that set it? UPDATE tbl SET col=LAST_INSERT_ID(col+1) If you then do SELECT LAST_INSERT_ID() or use the equivalent API Insert_ID call, you'll get the new value of col. This trick is actually noted in the manual. Oh by the way, no locks required for all this. Regards, Arjen. -- MySQL Training in Sydney: 9-13 Sep 2002, https://order.mysql.com/?marl __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com --------------------------------------------------------------------- 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
