LAST INSERT ID MySQL

Question

I have a MySQL question that I think must be quite easy  I need to return the LAST INSERTED ID from table1 when I run the following MySql query   INSERT INTO table1  title userid  VALUES   test  1    INSERT INTO table2  parentid otherid userid  VALUES  LAST INSERT ID   4 1   SELECT LAST INSERT ID      As you can understand the current code will just return the LAST INSERT ID of table2 instead of table1  how can I get the id from table1 even if I insert into table2 between

User · Answer

This enables you to insert a row into 2 different tables and creates a reference to both tables too   START TRANSACTION  INSERT INTO accounttable account username       VALUES  AnAccountName    INSERT INTO profiletable profile account id       VALUES   SELECT account id FROM accounttable WHERE account username  AnAccountName         SET  profile id   LAST INSERT ID     UPDATE accounttable SET  account profile id     profile id  COMMIT

User · Answer

Just to add for Rodrigo post  instead of LAST INSERT ID   in query you can use SELECT MAX id  FROM table1   but you must use      INSERT INTO table1  title userid  VALUES   test   1  INSERT INTO table2  parentid otherid userid  VALUES    SELECT MAX id  FROM table1   4  1

User · Answer

You could store the last insert id in a variable    INSERT INTO table1  title userid  VALUES   test   1    SET  last id in table1   LAST INSERT ID    INSERT INTO table2  parentid otherid userid  VALUES   last id in table1  4  1         Or get the max id frm table1  INSERT INTO table1  title userid  VALUES   test   1    INSERT INTO table2  parentid otherid userid  VALUES  LAST INSERT ID    4  1    SELECT MAX id  FROM table1

User · Answer

Instead of this LAST INSERT ID   try to  use this one   mysqli insert id connection

User · Answer

I had the same problem in bash and i m doing something like this   mysql -D  dbname  -e  insert into table1  myvalue  values     foo        which works fine -  But  mysql -D  dbname  -e  insert into table1  myvalue  values     foo    set  last insert id   LAST INSERT ID     mysql -D  dbname  -e  insert into table2  id tab1  values   last insert id      don t work  Because after the first command  the shell will be logged out from mysql and logged in again for the second command  and then the variable  last insert id isn t set anymore  My solution is   lastinsertid   mysql -B -N -D  dbname  -e  insert into table1  myvalue  values     foo    select LAST INSERT ID      mysql -D  dbname  -e  insert into table2  id tab1  values    lastinsertid       Maybe someone is searching for a solution an bash  -

User · Answer

It would be possible to save the last id in table1 variable into a php variable to use it later    With this last id I need to attach some records in another table with this last id  so I need    1  Do an INSERT and get the last id in table1   INSERT into Table1 name  values   AAA     SET  last id in table1   LAST INSERT ID      2  For any indeterminated rows in another table  UPDATING these rows with the last id insert generated in the insert     element   array some ids      foreach   element as  e             UPDATE Table2 SET column1    last id in table1 WHERE id    e

User · Answer

For no InnoDB solution  you can use a procedure don t forgot to set the delimiter for storing the procedure with   CREATE PROCEDURE myproc OUT id INT  IN otherid INT  IN title VARCHAR 255   BEGIN LOCK TABLES  table1  WRITE  INSERT INTO  table1     title    VALUES    title     SET  id   LAST INSERT ID    UNLOCK TABLES  INSERT INTO  table2     parentid    otherid    userid    VALUES   id   otherid  1    END  And you can use it    SET  myid  CALL myproc   myid  1   quot my title quot     SELECT  myid

User · Answer

We only have one person entering records  so I execute the following query immediately following the insert    result    conn- gt query  SELECT   FROM corex ORDER BY id DESC LIMIT 1     while   row    result- gt fetch assoc                    id    row  id         This retrieves the last id from the database

User · Answer

Since you actually stored the previous LAST INSERT ID   into the second table  you can get it from there   INSERT INTO table1  title userid  VALUES   test  1    INSERT INTO table2  parentid otherid userid  VALUES  LAST INSERT ID   4 1   SELECT parentid FROM table2 WHERE id   LAST INSERT ID

User · Answer

If you need to have from mysql  after your query  the last auto-incremental id without another query  put in your code   mysql insert id

User · Answer

For last and second last   INSERT INTO  t parent user   u id    p id   VALUES   SELECT MAX u id-1  FROM user    SELECT MAX u id  FROM user

[mysql] LAST_INSERT_ID() MySQL

Examples related to mysql

Examples related to insert

Examples related to last-insert-id