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

User · Accepted Answer

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!

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Your approach is good but the problem is that you use  quot   quot  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

User · Answer

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

User · Answer

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

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

Examples related to mysql