How to Copy Rows and Insert Them into Same Table Using MySQLi?
Last updated on October 17, 2022
Rows duplicates from the same table are the easiest task. Sometime backend developers face this kind of task. It is lengthy and painful work unless you don’t find a way to do it quickly. In this article, we will learn how to copy rows and insert them into the same table. Let’s say we have a colors table that has colors for different parts of a vehicle. Following are the columns
- id
- color_name
- vehicle_id
Let’s say, the colors table has 10 rows that contain different colors of vehicle parts for vehicle_id=1. Here, vehicle_id is the foreign key that is the primary key of the vehicles table.
Now, these 10 rows need to be duplicated for the vehicle_id=5. So our goal is to copy rows that have vehicle_id=1 and paste them into the same table with vehicle_id=5.
insert into colors(color_name, vehicle_id)
select color_name, 5
from colors
where vehicle_id = 1
How does this query work? Let’s understand it. First, the MySQLi query sets the columns in which data has to insert. Then it selects columns for copying the data that has to insert into new rows.
In the second line of the above query, we have added static value 5. It is because we want to create rows for vehicle_id=5. So if we don’t add the static value 5 MySQLi query would keep the vehicle_id=1 for new rows.
In the last row, we have where clause that tells to query select the rows which have vehicle_id=1. In this say MySQLi query would copy the rows of vehicle_id=1 and insert them into the same table with vehicle_id=5.
This method can be used to duplicate hundreds or thousands of rows. It would take barely a few seconds to complete the task. The good thing is it is a safe way. There won’t be an issue with old rows of the table.
Related Posts
- 1. MySQL SELECT Statement
- 2. How to search value in JSON array through MySQLi query?
- 3. Best Way to Create Reliable Database Design for Multi-Language