How can I move existing data to new row?

Question

I have a table with several thousand rows. I'd like to be able to take one of those rows and duplicate it into a new row. What is the best way to do this?

Should I grab the rows data and assign each field into a variable? And then take those variables and insert them into a new row?

So that'd be two queries -- is there a more efficient way of doing this?

Answer 1

INSERT INTO tablename SELECT * FROM tablename WHERE <your where condition>

If a primary key is in the way

INSERT INTO tablename (every, field, except, primary) SELECT every, field, except, primary FROM tablename WHERE <your where condition>

OR

INSERT INTO tablename SELECT NULL, every, field, except, primary FROM tablename WHERE <your where condition>

(assuming the id is the first column)

Explicitly naming the columns you're going the use (the 2nd query in this answer) should be preferred for production code.

Answer 2

Check out mysql's INSERT ... SELECT syntax.

You should be able to do something like:

INSERT INTO tbl (a, b, c) SELECT a, b, c FROM tbl WHERE id = 1234;