CODEWITHSUNDEEP
mysql
Learner
Learner

Reputation: 786

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

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.

Upvotes: 0

Views: 81

Answers (2)

Lee S
Lee S

Reputation: 343

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)

Upvotes: 2

fancyPants
fancyPants

Reputation: 51868

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);

Upvotes: 2

Related Questions