Copy autoincrement value to another column on insert?

Question

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.

Bohemian · Accepted Answer · 2012-11-26T11:16:51.563

2

Use the LAST_INSERT_ID() function:

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

edited Nov 26 '12 at 11:16

answered Nov 26 '12 at 11:11

Bohemian

412,405
93
575
722

2

That's essentially what we are doing, but I was wondering if there was a way to link the two values during the insert? – Hailwood Nov 26 '12 at 11:12
You can query a catalog table to find the next increment value. I forget which one it is – Bohemian Nov 26 '12 at 11:18
5

This is not an answer to question asked – inemanja Nov 06 '16 at 00:54

score 0 · Answer 2 · edited Aug 13 '14 at 18:45

0

This is the single query:

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

edited Aug 13 '14 at 18:45

tkocmathla

901
11
24

answered Aug 13 '14 at 18:39

Narendra.A

11

1

There is no guarantee this will work if you have more than one table where you are inserting rows. – Erik S Aug 01 '16 at 13:06

Copy autoincrement value to another column on insert?

2 Answers2

Linked