Menu
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.
464
Here's the syntax of ALTER TABLE statement, ALTER TABLE table_name MODIFY column_name INT NOT NULL AUTO_INCREMENT PRIMARY KEY; In the above statement, you need to specify the table_name and column_name. Here's the SQL statement to add AUTO INCREMENT constraint to id column.
You can insert into an auto-increment column and specify a value. This is fine; it simply overrides the auto-increment generator. If you try to insert a value of NULL or 0 or DEFAULT , or if you omit the auto-increment column from the columns in your INSERT statement, this activates the auto-increment generator.
The starting value for AUTO_INCREMENT is 1, which is the default. It will get increment by 1 for each new record. To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT. Creating a table, with “id” as auto-increment.
It could have multiple causes: Check if the auto_increment value on the table itself, has the next highest value. Mind that if you have transactions where you INSERT a row and rollback the transaction, that auto_increment value will be gone/skipped.
Use the LAST_INSERT_ID() function:
update table whatever set
product_id = id
where id = last_insert_id()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
