> >> what if i need the primary key of a row that is to be inserted? that > > primary > >> key will be used to insert another row from another table.. what i > >> was > > doing > >> was after inserting the row, i get the primary key by select > >> statement and use that value to insert a row from another table.. is > >> this ok? are there any more efficient ways of doing this? > > > > I take it you mean a "primary key value that comes from an > > auto-increment column"? > > > > Next, we have to assume you're doing this: > > > > insert into mytable ( ... ) values ( ... ) > > > > select max(ID-column) from mytable > > > > re-use the value to insert child records? > > > > Is that correct? > > > > If so, I'd say this will ONLY work properly in a multi-user system if > > you're using transactions and you don't commit between the actual > > INSERT and SELECT. > > > > > > It's a pity that MySQL doesn't understand the INSERT INTO ... > > RETURNING syntax, as this would solve your problem :-) > > > > i agree that if he's trying to select the highest value in some manner > as you describe he's in trouble, but there is of course > "last_insert_id()", which does solve his problem. > <http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#funct > ion_last-insert-id>
Ah yes, I was searching for that in the docs but couldn't remember it :-) Thanks! Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
