copy

Copy a record within the same table MySQL

Just a simple copy record function.

First of all, pick a random temporary table name, and ensure you don’t have a table in your database by that name.

Then, copy the record you want to copy, from the source table, into a temp table:

CREATE TEMPORARY TABLE <temp_table>
    SELECT * FROM <source_table>
    WHERE <source_table_id_field> = <id_to_copy>;

Because we want the new record to have a new ID number, lets drop that field from the temporary table:

ALTER TABLE <temp_table> 
    DROP <source_table_id_field>;

Now we just copy it back in, but because we are one field short (the ID one) then we just pass a zero instead and let MySQL go and get a new ID from the auto increment engine.:

INSERT INTO <source_table>
    SELECT 0, <temp_table>.* FROM <temp_table>;

Job done. Just need to tidy up and drop the temporary table (careful you delete the right one)

DROP TABLE <temp_table>;

PS. This will only work if your auto increment ID field is the first field in the table (which is generally the case for every database table I have ever worked with)