How to create stored procedure that inserts both input and data from select statement after first insert

Question

I am trying to create a stored procedure in MySQL that will add rows to two different tables. The first table (sites) has an id column set to auto_increment which I would like to include in the second insert statement to the sitesByUser table. I've tried some ideas based off this excellent post: https://dba.stackexchange.com/questions/2973/how-to-insert-values-into-a-table-from-a-select-query-in-postgresql but I get various errors, such as the one listed below. I suspect that part of my problem is that I'm trying to both add both userInput and SELECT id FROM sites WHERE id=LAST_INSERT_ID() to the same table, but I'm not sure what to do to get that to work.

CREATE PROCEDURE createSite(IN siteName VARCHAR(2048), IN userInput VARCHAR(255))
BEGIN
INSERT INTO sites(siteName, user) VALUES (siteName, userInput);
INSERT INTO sitesByUser(user, site) userInput, SELECT id FROM sites WHERE id=LAST_INSERT_ID();
SELECT * FROM sitesByUser WHERE id=LAST_INSERT_ID();
END

The response from MySQL:

Error while performing Query.
ER_PARSE_ERROR
ER_PARSE_ERROR: 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 'userInput, SELECT id FROM sites WHERE id=LAST_INSERT_ID();
SELECT * FROM sitesBy' at line 3

score 1 · Answer 1 · answered Dec 13 '17 at 05:13

1

Try this

SET @last_id_in_table1 = LAST_INSERT_ID();
SELECT * FROM sitesByUser WHERE id=@last_id_in_table1;

Hope this helps

answered Dec 13 '17 at 05:13

was it helpful? – Dec 13 '17 at 05:26

score 0 · Accepted Answer · answered Dec 13 '17 at 06:25

It seems that the correct way to do this is to store the LAST_INSERT_ID() in a variable as described here: How to declare a variable in MySQL? I'm not sure that I should be using the @ symbol in front of the variable since that seems to make it a user-defined variable which means it is session-specific which is probably too wide in scope for my needs, but so far, this successfully creates a stored procedure that I think will work. I'll update this post if it does not.

CREATE PROCEDURE createSite(IN siteName VARCHAR(2048), IN userInput VARCHAR(255))
BEGIN
INSERT INTO sites(siteName, user) VALUES (siteName, userInput);
SET @last_id_in_sites = LAST_INSERT_ID();
INSERT INTO sitesByUser(user, site) VALUES (userInput, @last_id_in_sites);
SELECT * FROM sitesByUser WHERE id=LAST_INSERT_ID();
END

How to create stored procedure that inserts both input and data from select statement after first insert

2 Answers2