Bitz Bitz - 2 months ago 7
MySQL Question

Pulling an data from a nested INSERT statement using SELECT?

Is it possible to have an INSERT statement return the columns affected into a SELECT statement?
For example, I have the statement:

INSERT INTO work_day (WorkDateId, TimeframeId) VALUES (@selecteddateid,@timeframeid);


But work_day has an auto incrementing, work_dayId, that gets created when this row data is inserted. I want to put this work_dayId in another statement, but I was wondering if it would be able to nest this INSERT inside a SELECT that will select the affected/created row.

So would I be able to place this statement like so:

INSERT INTO appointment
(customerid, WorkDayId, UserId, Priority, Assign)
VALUES
(@otherdata
(SELECT WorkDayId FROM work_day WHERE WorkDateId = (INSERT INTO work_day (WorkDateId, TimeframeId) VALUES (@selecteddateid,@timeframeid))));

Answer

AS @Gordon stated:

No, you cannot do that. You want last_insert_id()

Posting it here as an answer for better visibility.