List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:May 25 2001 8:31pm
Subject:RE: LAST_INSERT_ID Issues
View as plain text  
At 10:39 AM -0400 5/25/01, Dave Carter wrote:
>So in my insert into .... select, I would just add another Select???? Like
>this:
>
>INSERT INTO tablename (field1,field2,field3)
>SELECT table2.field1, table2.field2, table2.field3
>FROM table2
>SELECT LAST_INSERT_ID();

Uh, no.  Perhaps you should tell us exactly what you're trying to do,
because we're just guessing.  The statement above isn't legal, but even
if it were, it's not clear what use you're trying to make of LAST_INSERT_ID().

If what you're doing is inserting a record into one table that generates
an AUTO_INCREMENT value, and then trying to create a record in a second
table that uses the new value, you'd do something like this:

INSERT INTO first_table ...

INSERT INTO second_table (id,other_field1,other_field2,...)
        VALUES (LAST_INSERT_ID(),    # this is the new AUTO_INCREMENT value
                value_for_other_field1,
                value_for_other_field2,
                ...);

>
>
>Dave Carter
>Chief Web Architect
>Accelerated Business Technologies, Inc.
>http://www.abti.cc
>717.464.2970
>
>Filter Lovin: sql, database, query
>
>-----Original Message-----
>From: Billy Passauer [mailto:bp@stripped]
>Sent: Friday, May 25, 2001 10:18 AM
>To: Dave Carter
>Subject: RE: LAST_INSERT_ID Issues
>
>
>>  -----Original Message-----
>>  From: Dave Carter [mailto:dave_carter@stripped]
>>
>>  This does not return any results
>>
>>  SELECT LAST_INSERT_ID() FROM tablename;
>
>What did you do prior to executing the statement?  If you didn't do an
>INSERT into a table that has an autoincrement field (while using the
>same connection), you won't see any results.  Well, actually I just
>tried that and it returned 0 (zero) - is that what you mean when you
>say it doesn't return any results?  If I do what you said, I get a
>zero returned for each row that is in the table.
>BTW, drop the "FROM tablename", the syntax is just: SELECT
>LAST_INSERT_ID();
>
>  ... Billy ...


-- 
Paul DuBois, paul@stripped
Thread
LAST_INSERT_ID IssuesDave Carter25 May
  • Re: LAST_INSERT_ID IssuesPaul DuBois25 May
    • RE: LAST_INSERT_ID IssuesDave Carter25 May
RE: LAST_INSERT_ID IssuesGhislain Proulx25 May
RE: LAST_INSERT_ID IssuesDave Carter25 May
  • RE: LAST_INSERT_ID IssuesPaul DuBois25 May