0

Is there a way to insert a row, get the inserted id and use it in the next query all this in just one single query?

INSERT INTO tableA (eID, name, otherStuff) VALUES (NULL, 'emailName', 'otherValues');
SET @tableA_id = SELECT LAST_INSERT_ID();
INSERT INTO tableB (id, emailId, body, name, langId) VALUES (NULL, @tableA_id, 'email text', 'Default', '1');
INSERT INTO tableB (id, emailId, body, name, langId) VALUES (NULL, @tableA_id, 'other language text', 'Default', '2');

In the previous code segment, there are three separate queries and when I execute them in my sql editor it gives me an error here: SET @tableA_id = LAST_INSERT_ID();

Again, there are 3 separate queries here, is there a way to have something like a subquery in order to achieve this?

HerbertRedford
  • 230
  • 4
  • 14
  • 1
    What is the error? Are you declaring the variable first? Can you insert null into the eID field? We need error details – Brad Sep 12 '19 at 14:37
  • @Brad there's no specific error. This is what I'm getting back: ```[ERROR in query 2] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT LAST_INSERT_ID()' at line 1``` – HerbertRedford Sep 12 '19 at 14:46
  • But, there's no code `SELECT LAST_INSERT_ID()` within the block you presented in the question. – Barbaros Özhan Sep 12 '19 at 14:48
  • Yes there is @BarbarosÖzhan, it's after the first `INSERT INTO`. Second line – HerbertRedford Sep 12 '19 at 15:21
  • No, that's `SET @tableA_id = LAST_INSERT_ID();` – Barbaros Özhan Sep 12 '19 at 15:22
  • Indeed, my mistake. It was a copy/paste mistake. Already corrected. The error's still there – HerbertRedford Sep 12 '19 at 15:41
  • The error is coming from `SET`. If I execute only `LAST_INSERT_ID()` I'm getting a value, but if I run `SET @tableA_id = SELECT LAST_INSERT_ID();` that's when I get the error – HerbertRedford Sep 12 '19 at 15:47

1 Answers1

0

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;

Or We have inserted 3 records above. Therefore, the next id must be 4.

The following is the syntax to know the next id.

SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = "yourDatabaseName"
AND TABLE_NAME = "yourTableName"
ThemeMora
  • 1
  • 1
  • Please don't copy code from [other answers](https://stackoverflow.com/questions/3837990/last-insert-id-mysql/3838011#3838011) without attribution. – Nick Sep 14 '19 at 06:00