[mysql] Duplicate / Copy records in the same MySQL table

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.

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 

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;

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;

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.

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.

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.

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.

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