Duplicate / Copy records in the same MySQL table

Question

I have been looking for a while now but I can not find an easy solution for my problem. I would like to duplicate a record in a table, but of course, the unique primary key needs to be updated.

I have this query:

INSERT INTO invoices     SELECT * FROM invoices AS iv WHERE iv.ID=XXXXX     ON DUPLICATE KEY UPDATE ID = (SELECT MAX(ID)+1 FROM invoices) 

the problem is that this just changes the ID of the row instead of copying the row. Does anybody know how to fix this ?

//edit: I would like to do this without typing all the field names because the field names can change over time.

Answer 1

The way that I usually go about it is using a temporary table. It's probably not computationally efficient but it seems to work ok! Here i am duplicating record 99 in its entirety, creating record 100.

CREATE TEMPORARY TABLE tmp SELECT * FROM invoices WHERE id = 99;  UPDATE tmp SET id=100 WHERE id = 99;  INSERT INTO invoices SELECT * FROM tmp WHERE id = 100; 

Hope that works ok for you!

Answer 2

Alex's answer needs some care (e.g. locking or a transaction) in multi-client environments.

Assuming the AUTO ID field is the first one in the table (a usual case), we can make use of implicit transactions.

     CREATE TEMPORARY TABLE tmp SELECT * from invoices WHERE ...;     ALTER TABLE tmp drop ID; # drop autoincrement field     # UPDATE tmp SET ...; # just needed to change other unique keys     INSERT INTO invoices SELECT 0,tmp.* FROM tmp;     DROP TABLE tmp; 

From the MySQL docs:

Using AUTO_INCREMENT: You can also explicitly assign NULL or 0 to the column to generate sequence numbers.