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
Let’s say, the colors table has 10 rows that contain different colors of vehicle parts for
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
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
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.