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?
--
View this message in context: http://www.nabble.com/what-if-i-need...html#a12313472
Sent from the MySQL - General mailing list archive at Nabble.com.

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

yup, that's it.. thanks for the reply..
--
View this message in context: http://www.nabble.com/what-if-i-need...html#a12313942
Sent from the MySQL - General mailing list archive at Nabble.com.

> >> 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/...ons.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

On 8/24/07, hardc0d3r <hardc0d3r@gmail.com> wrote:
>
> yup, that's it.. thanks for the reply..
> --


More typically, you would use the last_insert_id() function which will
return the value of the most recently created auto-increment key...

CREATE TABLE mytable (
id int auto_increment primary key,
foo char(1)
);

INSERT INTO mytable (foo) VALUES('a');
SELECT LAST_INSERT() AS newkey;

calculating the max key + 1 , as it has been noted, must be done
transactionally and will therefore require a transactional table
type... The catch is that, unlike MyISAM, Innodb does not maintain
column meta information like min, max, sum so this approach will get
more expensive with each subsequent call.
--
- michael dykman
- mdykman@gmail.com

- All models are wrong. Some models are useful.

On Fri, 24 Aug 2007, Michael Dykman wrote:

> calculating the max key + 1 , as it has been noted, must be done
> transactionally and will therefore require a transactional table

And with a transaction isolation level greater than Read Commited...

-r