From: Martijn Tonies
Date: August 24 2007 2:48pm
Subject: Re: what if i need the primary key of a row that is to be inserted?
List-Archive: http://lists.mysql.com/mysql/208705
Message-Id: <058501c7e65d$dc121cb0$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 :-)

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com