Get LAST_INSERT_ID() after INSERT and use it in following queries

Question

I'm inserting a row in one table like:

INSERT INTO first (name) VALUES ('something');

and then get the auto-incremented ID of that row like:

SELECT LAST_INSERT_ID();

Then, I want to use this particular ID in a series of INSERTs like:

INSERT INTO second (f,a,b) VALUES (ID, 'something else', 'with something else');
INSERT INTO second (f,a,b) VALUES (ID, 'something else 1', 'with something else 3');
INSERT INTO second (f,a,b) VALUES (ID, 'something else 2', 'with something else 4');

The question is: how do I "assign" that first LAST_INSERT_ID() to some sort of variable, so that I can reuse it in the following queries. I've tried with WITH and AS and all that, but none of that worked.

(basically my programming mind is craving for something like: ID = SELECT LAST_INSERT_ID(); and then go on to execute the next queries... but I cannot figure out how to do it.)

This question has the [sqlite] tag but no programming language tag. SQLite is almost always used within applications written in some programming language which is most certainly not SQL. It is almost trivial to get the result of `SELECT LAST_INSERT_ID();` into a variable of your programming language and then place it into your `INSERT` statements, but if you don't tell us which programming language you are using, (and perhaps show some code,) there is not much we can do to help. — Mike Nakis, Jan 12 '21 at 14:23

score 0 · Answer 1 · answered Jan 12 '21 at 10:39

SQLite does not support variables in sql statements.

If you have defined the column ID of the table first as:

ID INTEGER PRIMARY KEY AUTOINCREMENT

then you know that the last inserted value is the max ID of the table, so you can insert the rows to the table second like this:

INSERT INTO second (f,a,b) VALUES ((SELECT MAX(ID) FROM first), 'something else', 'with something else');

if you are sure that there is at least 1 row in first.

Or better:

WITH 
  cte(a, b) AS (
    VALUES ('something else', 'with something else'),
           ('something else 1', 'with something else 3'),
           ('something else 2', 'with something else 4')
  )
INSERT INTO second (f, a, b) 
SELECT f.id, c.a, c.b
FROM (SELECT MAX(id) id FROM first) f
CROSS JOIN cte c
WHERE f.id IS NOT NULL

See the demo.

Get LAST_INSERT_ID() after INSERT and use it in following queries

1 Answers1