Copy autoincrement value to another column on inse

问题:

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.

回答1:

Use the LAST_INSERT_ID() function:

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

回答2:

This is the single query:

insert into whatever 
set product_id = last_insert_id() + 1;