CODEWITHSUNDEEP
mysql
user3272483
user3272483

Reputation: 408

Using where id = last_insert_id() returns empty result set

I am trying to fetch a row of data in mysql after inserting a row of data in mysql table using

SELECT * FROM incoming_sms WHERE id = last_insert_id(); but this returns an empty set of results although i have indeed inserted a new row and my table contains data.

How can i make my query return some data?.

Upvotes: 1

Views: 1244

Answers (1)

spencer7593
spencer7593

Reputation: 108500

The normative pattern is to execute a SELECT LAST_INSERT_ID() statement immediately following the successful insert statement, from the same session that issue the INSERT statement.

There are several possible reasons that what you are attempting is not working. I've not seen the LAST_INSERT_ID() function used in a SELECT statement like you have it; I'm not sure if the behavior is defined. I've just never seen it used like that.

The normal pattern is:

INSERT INTO mytable (col) VALUES (val);
-- check if statement succeeded; if it successfully inserted a row
SELECT LAST_INSERT_ID();
-- retrieve returned value

Then use the retrieved value in subsequent processing.

Note (again) that the reference to LAST_INSERT_ID() must be made from the same session that performed the INSERT, and should be performed before any other SQL statement is issued.

http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id

Upvotes: 1

Related Questions