From: Martijn Tonies
Date: August 24 2007 3:04pm
Subject: Re: what if i need the primary key of a row that is to be inserted?
List-Archive: http://lists.mysql.com/mysql/208707
Message-Id: <059d01c7e660$10015480$9902a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit


> >> 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