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 ?
posted on 03.06.2020 at 16:08
0pnshow more

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.
posted on 05.06.2020 at 17:34
0pnshow more
share on facebookshare on twitter
2021 AnswerTabsTermsContact us