Last Updated: February 25, 2016
·
3.882K
· hannesg

Mysql: set the LAST_INSERT_ID

Mysql has a really handy function LAST_INSERT_ID. If called without a param it returns the last inserted id as we all know it. But if you give it a param it sets the last inserted id of the connection. This way you can easily store the result of a computation in a cell and fetch it in one query.

Example using ruby + mysql2:

# table looks like this:
# +----+--------+
# | id + value  +
# +----+--------+
# | 1  | 1      |
# | 2  | 2      |
# +----+--------+
# client is a Mysql2::Client

client.query('UPDATE `table` SET
  `value` = LAST_INSERT_ID(`value`*3)
   WHERE `id` = 1;')

client.last_id # == 3

# the table now looks like this:
# +----+--------+
# | id + value  +
# +----+--------+
# | 1  | 3      |
# | 2  | 2      |
# +----+--------+

Nice!