Search code examples
mysqlinsertauto-increment

Copy autoincrement value to another column on insert?

Basically I have a table that versions products,

so it has two columns of interest, id | product_id

id is an autoincrement column,
product_id is just an int

When a product is first created the product_id comes from the id, When the product is edited we duplicate the row, so the product_id is the same, but the id is different. so when we first create the product we do two queries,

insert, then update table whatever set product_id = id where id = {the insert id}

This works, but I am wondering if there is a way to do it in one query?

Note we only have access to insert, update, delete. no triggers or stored procedures.

Solution

  • Use the LAST_INSERT_ID() function:

    update table whatever set
product_id = id
where id = last_insert_id()