How to use last_insert_id() function INSIDE an INSERT query

Databases
1

so i’ve got an insert query, puts a reference to a file on the system in a table. good, great. Now i’ve got another table ( a join table?) that has 3 columns (id, fileId, entryId) that i need to make an association on. I’ve got the entryId, but the fileId is what I don’t have but can get with the last_insert_id() function but don’t know how to ‘use’ it.

Is this right?


INSERT INTO siteentryattachements(entryId, fileId)
VALUES ('#ARGUMENTS.entryId#','SELECT LAST_INSERT_ID() as fileId');
2 
INSERT INTO siteentryattachements(entryId, fileId)
VALUES ('#ARGUMENTS.entryId#',LAST_INSERT_ID());

you don’t need to alias the columns in your VALUES clause.

3

but how would i get the last_insert_id from the previous table? i’m not wanting the last_entry_id() from the siteentryattachments table, but rather from another table.

EDIT: okay, so does mysql automatically just create a value of the last_inserted_id() of ALL tables then?

4

last_insert_id() returns the last auto_increment value from the previous statement. for example:

mysql> create table a (i int auto_increment, primary key (i));
Query OK, 0 rows affected (0.07 sec)

mysql> create table b (i int auto_increment, a int, primary key (i));
Query OK, 0 rows affected (0.03 sec)

mysql> insert a values (null);
Query OK, 1 row affected (0.05 sec)

mysql> insert a values (null);
Query OK, 1 row affected (0.00 sec)

mysql> insert a values (null), (null);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from a;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
4 rows in set (0.00 sec)

notice that the last auto_increment value generated is 4, so the next call to last_insert_id() will be 4.

mysql> insert b values (null, last_insert_id());
Query OK, 1 row affected (0.03 sec)

the b table also has an auto_increment column, so last_insert_id() will now return 1.

mysql> insert b select null, last_insert_id() from a;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from b;
+----+------+
| i  | a    |
+----+------+
|  1 |    3 |
|  2 |    1 |
|  3 |    1 |
|  4 |    1 |
|  5 |    1 |
+----+------+
5 rows in set (0.00 sec)

notice that the multiple calls to last_insert_id() in the previous insert statement all returned 1. this allows you to insert multiple rows in a one-to-many relationship in a single statement.