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:

    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)

By admin

2 thought on “Copy a record within the same table MySQL”
    1. You’d have to use the LAST_INSERT_ID() function:


      But obviously, this would only return the last inserted one. So if you duplicated loads of records, you’d only get the ID for the last one.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.