List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 22 2000 10:52pm
Subject:Re: Problem with LAST_INSERT_ID( )
View as plain text  
At 2:41 PM -0800 2000-03-22, lucy vu wrote:
>Hi,
>
>I don't know if there is a problem with
>LAST_INSERT_ID() function.
>
>I have 2 following tables:
>main table
>CREATE TABLE publication
>( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
>   title VARCHAR(60) NOT NULL,
>   abstract LONGTEXT,
>   PRIMARY KEY (id));
>
>CREAT TABLE author_list
>( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
>   lastn CHAR(40) NOT NULL,
>   firstn CHAR(40) NOT NULL,
>   main_id SMALLINT UNSIGNED NOT NULL REFERENCES
>publication,
>   PRIMARY KEY (id));
>
>if I use the following statement to insert the data
>into those table,
>
>INSERT INTO publication VALUES
>(NULL, 'Computer Network', 'whatever');
>
>INSERT INTO author_list VALUES
>(NULL, 'Smith', 'D', LAST_INSERT_ID()),
>(NULL, 'Rose', 'W', LAST_INSERT_ID()),
>(NULL, 'White', 'P', LAST_INSERT_ID()));
>
>it gives me the correct main_id from author_list table
>which is referenced from id in publication tables;
>
>However if I insert data as following manner:
>
>INSERT INTO publication VALUES
>(NULL, 'Computer Network', 'whatever');
>
>INSERT INTO author_list VALUES
>(NULL, 'Smith', 'D', LAST_INSERT_ID());
>
>INSERT INTO author_list VALUES
>(NULL, 'Rose', 'W', LAST_INSERT_ID());
>
>INSERT INTO author_list VALUES
>(NULL, 'White', 'P', LAST_INSERT_ID());
>
>Which I use separate INSERT statement to insert data
>into author_list table,
>
>Then the main_id in the author_list table is messed
>up. Since the data that I get from the web is needed
>to use separate INSERT statement.  Could you please
>help me to fix this problem?


Your problem is that author_list has its own AUTO_INCREMENT
field, so each LAST_INSERT_ID() above refers to the id
generated in the previous statement.  For your first INSERT
into the author_list table, that id will be the one generated
for the publication table, but for the second and third INSERTs
into the author_list table, that id will be the one generated
for the previous insert into the author_list table.

If you're getting your data from the Web, are you using some
sort of programming language to generate the statements?
If so, you should save the value of the inserted id from
publication and use its value for all the inserts into
author_list.

-- 
Paul DuBois, paul@stripped
Thread
Problem with LAST_INSERT_ID( )lucy vu23 Mar
  • Re: Problem with LAST_INSERT_ID( )Paul DuBois23 Mar