MySQL: Duplicate record

Today I had to duplicate a record in a MySQL database. Of course the primary key  (which is AUTO_INCREMENT in my case) has to be unique. This is what I came up with:

CREATE TEMPORARY TABLE tmp SELECT * FROM order WHERE idorders=10017332;
ALTER TABLE tmp DROP idorders;
INSERT INTO orders SELECT 0, tmp.* from tmp;

Line one copies the record that should be duplicated from the idorders table to a temporary table (which drops itself when the MySQL session/connection is closed).
Line two drops the idorders column of the temporary table, which contains the unique primary key. This way MySQL will generate a new unique key when the record is imported to the idorders table.
Line three imports the record from the temporary table to the idorders table. The 0 in the select statement is necessary to have the right count of columns for the insert process.

Et voilĂ : You have a duplicate record in idorders with it’s own unique primary key.

Tagged with: ,

Leave a Reply

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