Copy and update rows in the same table with autoincrement - SQL

I need to copy the specific range of rows into the same table with autoincrement and update them. How can I do it by using SQL INSERT INTO function, without creating of the new table ?
03 Jun 2020 at 04:08 PM
0pnshow more
onefiveonefive
Hi,

To copy the rows into the same table, you can use combination of INSERT and SELECT functions. The range of rows can be specified in conditions in SELECT statement.

For example, when you have a table with following columns:
item_id INT (10) NOT NULL auto_increment
item_name VARCHAR (150) NOT NULL
item_description TEXT
item_price VARCHAR (10) NOT NULL
item_category INT (6) NOT NULL
item_branch INT (6) NOT NULL
item_quantity INT (6) NOT NULL

You can use this solution to copy the specific values from specific rows into the same table:

$sql = "INSERT INTO items (item_name, item_description, item_price, item_category, item_branch, item_quantity) SELECT item_name, item_description, item_price, item_category, 7, 0 FROM items WHERE item_branch = 5 ;";

$query = mysqli_query($sql);

This SQL query will copy all items assigned to branch 5 to the new branch 7. Quantity of each item at branch 7 will be zero. Values in auto-increment column item_id will be added automatically.
05 Jun 2020 at 05:34 PM
0pnshow more
TimTim
Share on FacebookShare on TwitterShare on LinkedInSend email
Follow us on Facebook & Twitter
2021 AnswerTabsTermsContact us