Using LAST_INSERT_ID on MySQL

dev support
1

Hi,

In railo, we were able to use the MYSQL function LAST_INSERT_ID to return a value from an UPDATE statement

<cfquery … result=“updateResults”>
UPDATE in_nextid
SET nextid=LAST_INSERT_ID(nextid+1)
WHERE …

This singleton update would put the new value in updateResults.generatedKey.

It doesn’t seem to work on Lucee; after the UPDATE has executed, “generatedKey” is not in the structure.

EDIT: Using Lucee 5.2.3.35

Any ideas?

Regards, PJ

2

Hi Peter. My understanding is that GENERATED_KEY is only available when you are inserting into a table with an auto-incrementing primary key. Why would you expect it to be returned from an update when no new records are created?

Perhaps I’m misunderstanding what you’re trying to do.

3

You can also examine the query object after any database insert, dump it out and you will see it has a property of the new auto incremented field

4

In MYSQL “SELECT last_insert_id()” returns the last value created by the last auto-increment insert. last_insert_id(expr) sets the value of “last_insert_id()” to the value of expr. It’s a convenient way to get an updated value resulting from an update using a single statement and no transaction.

In my case if “nextid” in the table was 6, the update would set it to 7, and 7 would be returned by “SELECT last_insert_id()”.
ColdFusion and Ralio, both propagate this to generatedKey whereas Lucee doesn’t.

See https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id