Duplicate / Copy records in the same MySQL table
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!
You KNOW for sure, that the DUPLICATE KEY will trigger, thus you can select the MAX(ID)+1 beforehand:
INSERT INTO invoices SELECT MAX(ID)+1, ... other fields ... FROM invoices AS iv WHERE iv.ID=XXXXX
A late answer I know, but it still a common question, I would like to add another answer that It worked for me, with only using a single line insert into
statement, and I think it is straightforward, without creating any new table (since it could be an issue with CREATE TEMPORARY TABLE
permissions):
INSERT INTO invoices (col_1, col_2, col_3, ... etc)
SELECT
t.col_1,
t.col_2,
t.col_3,
...
t.updated_date,
FROM invoices t;
The solution is working for AUTO_INCREMENT
id column, otherwise, you can add ID
column as well to statement:
INSERT INTO invoices (ID, col_1, col_2, col_3, ... etc)
SELECT
MAX(ID)+1,
t.col_1,
t.col_2,
t.col_3,
... etc ,
FROM invoices t;
It is really easy and straightforward, you can update anything else in a single line without any second update statement for later, (ex: update a title column with extra text or replacing a string with another), also you can be specific with what exactly you want to duplicate, if all then it is, if some, you can do so.
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.