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)

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

Any ideas?

Regards, PJ

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.

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

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.