Duplicate / Copy records in the same MySQL table
MysqlMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
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!
Solution 2 - Mysql
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.
Solution 3 - Mysql
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
Solution 4 - Mysql
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.
Solution 5 - Mysql
Your approach is good but the problem is that you use "*" instead enlisting fields names. If you put all the columns names excep primary key your script will work like charm on one or many records.
INSERT INTO invoices (iv.field_name, iv.field_name,iv.field_name
) SELECT iv.field_name, iv.field_name,iv.field_name FROM invoices AS iv
WHERE iv.ID=XXXXX
Solution 6 - Mysql
I just wanted to extend Alex's great answer to make it appropriate if you happen to want to duplicate an entire set of records:
SET @x=7;
CREATE TEMPORARY TABLE tmp SELECT * FROM invoices;
UPDATE tmp SET id=id+@x;
INSERT INTO invoices SELECT * FROM tmp;
I just had to do this and found Alex's answer a perfect jumping off point!. Of course, you have to set @x to the highest row number in the table (I'm sure you could grab that with a query). This is only useful in this very specific situation, so be careful using it when you don't wish to duplicate all rows. Adjust the math as necessary.
Solution 7 - Mysql
I have a similar issue, and this is what I'm doing:
insert into Preguntas (`EncuestaID`, `Tipo` , `Seccion` , `RespuestaID` , `Texto` ) select '23', `Tipo`, `Seccion`, `RespuestaID`, `Texto` from Preguntas where `EncuestaID`= 18
Been Preguntas:
CREATE TABLE IF NOT EXISTS `Preguntas` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`EncuestaID` int(11) DEFAULT NULL,
`Tipo` char(5) COLLATE utf8_unicode_ci DEFAULT NULL,
`Seccion` int(11) DEFAULT NULL,
`RespuestaID` bigint(11) DEFAULT NULL,
`Texto` text COLLATE utf8_unicode_ci ,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=522 ;
So, the ID
is automatically incremented and also I'm using a fixed value ('23') for EncuestaID
.
Solution 8 - Mysql
I needed this as well; my solution was to use SQLYOG (free version) to export the desired record as SQL (creates an insert).
I then hand edited this to remove the id as this needs to be auto-generated and then copied the insert into SQLYog to execute it. This was painless. I guess plenty of other MySQL GUIs can do this as well.
This provides me with a record I can use for test purposes on a live system.
I now have this insert for reuse as well, as the table is rewritten daily.
Solution 9 - Mysql
Slight variation, main difference being to set the primary key field ("varname") to null, which produces a warning but works. By setting the primary key to null, the auto-increment works when inserting the record in the last statement.
This code also cleans up previous attempts, and can be run more than once without problems:
DELETE FROM `tbl` WHERE varname="primary key value for new record";
DROP TABLE tmp;
CREATE TEMPORARY TABLE tmp SELECT * FROM `tbl` WHERE varname="primary key value for old record";
UPDATE tmp SET varname=NULL;
INSERT INTO `tbl` SELECT * FROM tmp;
Solution 10 - Mysql
You can alter the temporarily table to change the ID field to a bigint or so without the NOT NULL requirement, then set the ID to 0 in that temp table. After that add it back to the original table and the NULL will trigger the auto increment.
CREATE TEMPORARY TABLE tmptable SELECT * FROM x WHERE (id='123');
ALTER TABLE tmptable CHANGE id id bigint;
UPDATE tmptable SET id = NULL;
INSERT INTO x SELECT * FROM tmptable;