Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (20.3k points)

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

2 Answers

0 votes
by (40.7k points)

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.

0 votes
by (3.1k points)

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. 

Related questions

0 votes
1 answer
asked Jul 24, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer

1.2k questions

2.7k answers

501 comments

693 users

Browse Categories

...