How to copy a row and insert in same table with a autoincrement field in MySQL?

Question

I have a table Eg- tab. What I am trying to do is copying a row with an autoincrement column ID=1 and insert the data into the same table with a row and column ID=2.

Using MySql. How can I do this in a single query? Please help

Answer 1

Try using INSERT ... SELECT in this way:

INSERT into your_table (c1, c2, ...)

SELECT c1, c2, ...

FROM your_table

WHERE id = 1

WHERE c1, c2, ... are all the columns except id. 

If you want to explicitly insert with an id of 2 then include that in your INSERT column list and your SELECT clause like this:

INSERT into your_table (id, c1, c2, ...)

SELECT 2, c1, c2, ...

FROM your_table

WHERE id = 1

Note: You need to take care of a possible duplicate id of 2 while writing the second query.

You can learn in-depth about SQL statements, queries and become proficient in SQL queries by enrolling in our industry-recognized Microsoft SQL Certification.

Answer 2

To copy a row and insert it into the same table in MySQL with an auto-increment field, you can use an INSERT INTO. SELECT statement. This lets you insert all the data from the source row while automatically generating a new value for the auto-increment column. 
 
Example 
Suppose we have a table named my_table with columns id (auto-increment), name, and value: 
 
INSERT INTO my_table (name, value) 
SELECT name, value 
FROM my_table 
WHERE id = 1; 
Explanation 
Specify the Columns: Just list which columns you wish to select and copy except 'id' since it contains the auto-increment specification. 
SELECT Clause: Here, it fetched the values from the same row which was identified. 
INSERT Clause: This created a new row and is able to let MySQL specify the id.