Selecting last inserted id and inserting into second table in MY SQL

Question

I am new to my sql and got stuck in a query. The query I am using is as follows;

insert into tableA(fname, lname) values('hello','world'); @id=SELECT LAST_INSERT_ID(); insert into tableB(x,y) values('good', @id);

but i am getting an error below;

#1064 - 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 '@id=SELECT LAST_INSERT_ID()' at line 2

please somebody suggest what is wrong I am doing here, thanks in advance.

Lee S · Accepted Answer · 2014-05-21 12:48:13Z

2

Close.

insert into tableA(fname, lname) values('hello','world'); SET @id = LAST_INSERT_ID(); insert into tableB(x,y) values('good', @id);

Result:

mysql> insert into tableA(fname, lname) values('hello','world'); Query OK, 1 row affected (0.07 sec) mysql> SET @id = LAST_INSERT_ID(); Query OK, 0 rows affected (0.00 sec)

edited May 21, 2014 at 12:48

answered May 21, 2014 at 12:22

Lee S

3431 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lee S Over a year ago

Sorry. That's what I get for copy and pasting your code without a coffee first. I've edited my answer, remove the 'SELECT' from the SET statement!!

idmean Over a year ago

What should "Close" mean? Please add some more relevant information to your answer, like a link to the documentation.

Lee S Over a year ago

Not close as in 'make not open', but close as in, you're very nearly right :)

fancyPants · Accepted Answer · 2014-05-21 12:21:37Z

You're missing set

insert into tableA(fname, lname) values('hello','world'); set @id=SELECT LAST_INSERT_ID(); insert into tableB(x,y) values('good', @id);

Collectives™ on Stack Overflow

Selecting last inserted id and inserting into second table in MY SQL

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related