List:General Discussion« Previous MessageNext Message »
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?
View as plain text  

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

Thread
what if i need the primary key of a row that is to be inserted?hardc0d3r24 Aug
  • Re: what if i need the primary key of a row that is to be inserted?Martijn Tonies24 Aug
    • Re: what if i need the primary key of a row that is to be inserted?hardc0d3r24 Aug
      • Re: what if i need the primary key of a row that is to be inserted?Michael Dykman24 Aug
        • Re: what if i need the primary key of a row that is to be inserted?Rudy Lippan24 Aug
  • Re: what if i need the primary key of a row that is to be inserted?Martijn Tonies24 Aug