Duplicate / Copy a record in MySQL

Under certain circumstances it may happen to have the need, within a web application or simply managing data in our MySQL database company, to make copies of entire records or portions of them.

Let's take an example. Let's say we have to manage the database of an e-commerce and may need to copy a sheet to insert a new one with the same characteristics as an existing one. Suppose, for example concrete, having to enter as a product of our hypothetical electronic store in the product "16GB iPod Nano" in the color "Silver" card with the same product to the already available "iPod Nano 16GB" color "White" .

Suppose that the iPod family of products are classified in the table lettori_mp3 structured as follows:

• ID
• brand
• model
• description
• color
• price

Suppose that the product you want to copy ("iPod Nano 16GB" color "White") has ID 123.

To make a copy of the record we will use this query:

 INSERT INTO lettori_mp3

 





 SELECT * FROM WHERE id = 123 lettori_mp3;

In this way we will create a record the same and we will just change the fields "ID" and "color" by manually specifying the values ​​for the new record.

Note, however, that if the ID field of our table was set as a numeric value with auto_increment unique view over the query will return an error (because you can not have two identical identifiers !!!). In this case we should select the fields you want to copy excluding the ID field:

 INSERT INTO lettori_mp3 (make, model, description, color, price)







 SELECT make, model, description, 'Silver', price lettori_mp3 FROM WHERE id = 123;

As you can see we have copied all the fields in the record except the field "ID" (for the reason noted above).
You may have noticed, too, that we have substituted in the SELECT field "color" (in the original record the color is "White") the value to insert in the new record ("Silver").

Et voila. You're done.
We just have to make a nice SELECT on our table to see if the new record has been entered correctly.

It 'hardly necessary to point out, finally, that you can make copies of all or part of records of different tables also (it is enough to specify the correct names of fields to copy and make sure that they accept the same data types).