In MySQL can I copy one row to insert into the same table

Question

insert into table select   from table where primarykey 1   I just want to copy one row to insert into the same table  i e   I want to duplicate an existing row in the table  but I want to do this without having to list all the columns after the  select   because this table has too many columns   But when I do this  I get the error      Duplicate entry  xxx  for key 1   I can handle this by creating another table with the same columns as a temporary container for the record I want to copy   create table oldtable temp like oldtable  insert into oldtable temp select   from oldtable where key 1  update oldtable tem set key 2  insert into oldtable select   from oldtable where key 2    Is there a simpler way to solve this

User · Answer

This can be achieved with some creativity:

SET @sql = CONCAT('INSERT INTO <table> SELECT null, 
    ', (SELECT GROUP_CONCAT(COLUMN_NAME) 
    FROM information_schema.columns 
    WHERE table_schema = '<database>' 
    AND table_name = '<table>' 
    AND column_name NOT IN ('id')), ' 
from <table> WHERE id = <id>');  

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

This will result in the new row getting an auto incremented id instead of the id from the selected row.

User · Answer

You could also try dumping the table  finding the insert command and editing it   mysqldump -umyuser -p mydatabase --skip-extended-insert mytable  gt  outfile sql   The --skip-extended-insert gives you one insert command per row  You may then find the row in your favourite text editor  extract the command and alter the primary key to  default

User · Answer

I just had to do this and this was my manual solution    In phpmyadmin  check the row you wish to copy At the bottom under query result operations click  Export  On the next page check  Save as file  then click  Go  Open the exported file with a text editor  find the value of the primary field and change it to something unique  Back in phpmyadmin click on the  Import  tab  locate the file to import  sql file under browse  click  Go  and the duplicate row should be inserted    If you don t know what the PRIMARY field is  look back at your phpmyadmin page  click on the  Structure  tab and at the bottom of the page under  Indexes  it will show you which  Field  has a  Keyname  value  PRIMARY    Kind of a long way around  but if you don t want to deal with markup and just need to duplicate a single row there you go

User · Answer

clone row with update fields and auto increment value  CREATE TEMPORARY TABLE  temp  SELECT   FROM  testing  WHERE id   14   UPDATE  temp  SET id    SELECT id FROM testing ORDER by id DESC LIMIT 1    1  user id   252  policy no    mysdddd12  where id   14   INSERT INTO  testing  SELECT   FROM  temp    DROP TEMPORARY TABLE IF EXISTS  temp

User · Answer

Update 07 07 2014 - The answer based on my answer  by Grim     is a better solution as it improves on my solution below  so I d suggest using that    You can do this without listing all the columns with the following syntax   CREATE TEMPORARY TABLE tmptable SELECT   FROM table WHERE primarykey   1  UPDATE tmptable SET primarykey   2 WHERE primarykey   1  INSERT INTO table SELECT   FROM tmptable WHERE primarykey   2    You may decide to change the primary key in another way

User · Answer

Create a table      CREATE TABLE  sample table            sample id  INT 10  unsigned NOT NULL AUTO INCREMENT          sample name  VARCHAR 255  NOT NULL          sample col 1  TINYINT 1  NOT NULL          sample col 2  TINYINT 2  NOT NULL         PRIMARY KEY   sample id          UNIQUE KEY  sample id    sample id          ENGINE  InnoDB  DEFAULT CHARACTER SET  utf8  COLLATE  utf8 general ci     Insert a row  INSERT INTO  sample table     VALUES NULL   sample name   1  2     Clone row insert above  INSERT INTO  sample table     SELECT      NULL AS  sample id   -- new AUTO INCREMENT PRIMARY KEY from MySQL      new dummy entry  AS  sample name    -- new UNIQUE KEY from you      sample col 1   -- col from old row      sample col 2  -- col from old row    FROM  sample table     WHERE  sample id    1    Test  SELECT   FROM  sample table

User · Answer

If your table s primary key field is an auto increment field  then you can use query with columns  For example  your table named test tbl has 3 fields as id  name  age  id is a primary key field and auto increment  so you can use the following query to duplicate the row   INSERT INTO  test tbl    name   age   SELECT  name   age  FROM  test tbl     This query results in duplicating every row     If your table s primary key field is not an auto increment field  then you can use the following  method   INSERT INTO  test tbl    id   name   age     SELECT 20  name   age  FROM  test tbl  WHERE id   19    The result of this query is a duplicate row of id 19 inserted as id 20

User · Answer

Some of the following was gleaned off of this site   This is what I did to duplicate a record in a table with any number of fields   This also assumes you have an AI field at the beginning of the table  function duplicateRow   id   1    dbLink     my db connection  qColumnNames   mysql query  SHOW COLUMNS FROM table   or die  mysql error     numColumns   mysql num rows  qColumnNames    for   x   0  x  lt   numColumns  x      colname     mysql fetch row  qColumnNames       sql    SELECT   FROM table WHERE tableId     id     row   mysql fetch row mysql query  sql     sql    INSERT INTO table SET    for  i 1  i lt count  colname -4  i      i set to 1 to preclude the id field   we set count  colname -4 to avoid the last 4 fields  good for our implementation   sql         colname  i  0             row  i             sql      CreateTime   NOW       we need the new record to have a new timestamp mysql query  sql    sql    SELECT MAX tableId  FROM table    res   mysql query  sql    row   mysql fetch row  res   return  row 0    gives the new ID from auto incrementing

User · Answer

You almost had it with the your first query you just need to specify the columns  that way you can exclude your primary key in the insert which will enact the auto-increment you likely have on the table to automatically create a new primary key for the entry   For example change this   insert into table select   from table where primarykey 1   To this   INSERT INTO table  col1  col2  col3   SELECT col1  col2  col3  FROM table  WHERE primarykey   1   Just don t include the primarykey column in either the column list for the INSERT or for the SELECT portions of the query

User · Answer

This procedure assumes that    you don t have  duplicate temp table your primary key is int you have access to create table   Of course this is not perfect  but in certain  probably most  cases it will work   DELIMITER    CREATE PROCEDURE DUPLICATE ROW copytable VARCHAR 255   primarykey VARCHAR 255   copyid INT  out newid INT  BEGIN         DECLARE EXIT HANDLER FOR SQLEXCEPTION SET  error 1          SET  temptable     duplicate temp table           SET  sql text   CONCAT  CREATE TABLE     temptable    LIKE    copytable           PREPARE stmt FROM  sql text          EXECUTE stmt          DEALLOCATE PREPARE stmt          SET  sql text   CONCAT  INSERT INTO     temptable    SELECT   FROM    copytable    where    primarykey      copyid           PREPARE stmt FROM  sql text          EXECUTE stmt          DEALLOCATE PREPARE stmt          SET  sql text   CONCAT  SELECT max    primarykey     1 FROM    copytable    INTO  newid            PREPARE stmt FROM  sql text          EXECUTE stmt          DEALLOCATE PREPARE stmt          SET  sql text   CONCAT  UPDATE     temptable    SET    primarykey     newid            PREPARE stmt FROM  sql text          EXECUTE stmt          DEALLOCATE PREPARE stmt          SET  sql text   CONCAT  INSERT INTO    copytable    SELECT   FROM     temptable               PREPARE stmt FROM  sql text          EXECUTE stmt          DEALLOCATE PREPARE stmt          SET  sql text   CONCAT  DROP TABLE     temptable           PREPARE stmt FROM  sql text          EXECUTE stmt          DEALLOCATE PREPARE stmt          SELECT  newid INTO newid  END    DELIMITER    CALL DUPLICATE ROW  table    primarykey   1   duplicate id   SELECT  duplicate id

User · Answer

I used in my Koha database to insert duplicate items with the  C  prefix in barcode column   INSERT INTO items   biblionumber    biblioitemnumber    barcode    dateaccessioned    SELECT  biblionumber    biblioitemnumber    CONCAT  C   barcode     dateaccessioned  FROM  items  WHERE barcode  14832

User · Answer

This solution showed above works perfect also for selected rows  For example I am creating demonstration rows for my nice2work project  and this works perfect   CREATE TEMPORARY TABLE tmptable SELECT   FROM myTable WHERE id 500  UPDATE tmptable SET id   0  UPDATE some fields I need to change INSERT INTO myTable SELECT   FROM tmptable  DROP TABLE tmptable       You can use this same also directly into your code like  PHP Style   sql    CREATE TEMPORARY TABLE tmptable SELECT   FROM myTable WHERE id 500  UPDATE tmptable SET id   0  UPDATE some fields I need to change INSERT INTO myTable SELECT   FROM tmptable DROP TABLE tmptable

User · Answer

max233 was certainly on the right track  at least for the autoincrement case  However  do not do the ALTER TABLE   Simply set the auto-increment field in the temporary table to NULL   This will present an error  but the following INSERT of all fields in the temporary table will happen and the NULL auto field will obtain a unique value

User · Answer

Here s an answer I found online at this site Describes how to do the above1 You can find the answer at the bottom of the page   Basically  what you do is copy the row to be copied to a temporary table held in memory  You then change the Primary Key number using update  You then re-insert it into the target table  You then drop the table   This is the code for it      CREATE TEMPORARY TABLE rescueteam ENGINE MEMORY SELECT   FROMfitnessreport4 WHERE rID 1   1 row affected    UPDATE rescueteam SET rID Null WHERE rID 1   1 row affected INSERT   INTO fitnessreport4 SELECT   FROM rescueteam   1 row affected    DROP TABLE rescueteam  MySQL returned an empty result set  i e  zero       rows     I created the temporary table rescueteam  I copied the row from my original table fitnessreport4  I then set the primary key for the row in the temporary table to null so that I can copy it back to the original table without getting a Duplicate Key error  I tried this code yesterday evening and it worked

User · Answer

I updated  LeonardChallis s solution as it didn t work for me as none of the others  I removed the WHERE clauses and SET primaryKey   0 in the temp table so MySQL auto-increments itself the primaryKey  CREATE TEMPORARY TABLE tmptable SELECT   FROM myTable  UPDATE tmptable SET primaryKey   0  INSERT INTO myTable SELECT   FROM tmptable    This is of course to duplicate all the rows in the table

User · Answer

I used Grim s technique with a little change  If someone looking for this query is because can t do a simple query due to primary key problem   INSERT INTO table SELECT   FROM table WHERE primakey 1    With my MySql install 5 6 26  key isn t nullable and produce an error    1048 - Column  primakey  cannot be null    So after create temporary table I change the primary key to a be nullable   CREATE TEMPORARY TABLE tmptable 1 SELECT   FROM table WHERE primarykey   1  ALTER TABLE tmptable 1 MODIFY primarykey int 12  null  UPDATE tmptable 1 SET primarykey   NULL  INSERT INTO table SELECT   FROM tmptable 1  DROP TEMPORARY TABLE IF EXISTS tmptable 1

User · Answer

Sorry for the necropost but this is what I turned up with google and since I found this helpful but problematic I wanted to contribute an important modification for anyone else who digs this up   First off  I m using SQL Server  not MySQL  but I think it should work similarly   I used Leonard Challis  solution because it was simplest and met the need  however there s a problem with this - if you simply take the PK and increment it by 1 then what happens if you ve added other records since the row in question was added   I decided it was best to just let the system handle the autoincrementing of the PK  so I did the following   SELECT   INTO  tmpTable FROM Table WHERE primarykey   1 --Optionally you can modify one or more fields here like this   --UPDATE  tmpTable SET somefield   newData ALTER TABLE  tmpTable DROP COLUMN TicketUpdateID INSERT INTO Tickets SELECT   FROM  tmpTable DROP TABLE  tmpTable   I believe this would work similarly in MySQL  but I can t test this  sorry

User · Answer

For a very simple solution  you could use PHPMyAdmin to export the row as a CSV file then simply import the amended CSV file  Editing the ID primarykey column to show a 0 for the primarykey value before you import it  SELECT   FROM table where primarykey 1  Then at the bottom of the page   Where is says  quot Export quot  simply export  then edit the csv file to remove the primarykey value  so it s empty  and then just import it into the database  a new primarykey will be assigned on import

User · Answer

Just wanted to post my piece of PHP code  because I think the way I collect the columns is a bit cleaner in code than the previous examples  Also this shows how you could easily alter an field  in this case adding a string  But you could also replace a foreign key field with the newly added record  in case you want to copy some child records as well        Read columns  unset the PK  always the first field in my case     stmt    conn- gt prepare  SHOW COLUMNS FROM template       stmt- gt execute        columns    stmt- gt fetchAll       columns   array map function   element    return  element  Field        columns      unset  columns 0          Insert record in the database  Add string COPY to the name field     sql    INSERT INTO  template     implode       columns         if   key   array search  name    columns          columns  key     CONCAT name    COPY        sql      SELECT   implode       columns    FROM  template  WHERE  id       id      stmt    conn- gt prepare  sql      stmt- gt execute

User · Answer

I would use below   insert into ORDER ITEM select   from ORDER ITEM where ITEM NUMBER  123

User · Answer

I used Leonard Challis s technique with a few changes   CREATE TEMPORARY TABLE tmptable 1 SELECT   FROM table WHERE primarykey   1  UPDATE tmptable 1 SET primarykey   NULL  INSERT INTO table SELECT   FROM tmptable 1  DROP TEMPORARY TABLE IF EXISTS tmptable 1    As a temp table  there should never be more than one record  so you don t have to worry about the primary key  Setting it to null allows MySQL to choose the value itself  so there s no risk of creating a duplicate   If you want to be super-sure you re only getting one row to insert  you could add LIMIT 1 to the end of the INSERT INTO line   Note that I also appended the primary key value  1 in this case  to my temporary table name

User · Answer

I might be late in this  but I have a similar solution which has worked for me    INSERT INTO  orders  SELECT MAX  order id   1  container id    order date    receive date    timestamp  FROM  orders  WHERE  order id    1   This way I don t need to create a temporary table and etc  As the row is copied in the same table the Max PK  1 function can be used easily   I came looking for the solution of this question  had forgotten the syntax  and I ended up making my own query  Funny how things work out some times   Regards

User · Answer

I m assuming you want the new record to have a new primarykey  If primarykey is AUTO INCREMENT then just do this   INSERT INTO table  col1  col2  col3       SELECT col1  col2  col3      FROM table   WHERE primarykey   1      where col1  col2  col3      is all of the columns in the table except for primarykey   If it s not an AUTO INCREMENT column and you want to be able to choose the new value for primarykey it s similar   INSERT INTO table  primarykey  col2  col3       SELECT 567  col2  col3      FROM table   WHERE primarykey   1      where 567 is the new value for primarykey

User · Answer

I know it s an old question  but here is another solution   This duplicates a row in the main table  assuming the primary key is auto-increment  and creates copies of linked-tables data with the new main table id   Other options for getting column names  -SHOW COLUMNS FROM tablename   Column name  Field  -DESCRIBE tablename            Column name  Field  -SELECT column name FROM information schema columns WHERE table name    tablename     Column name  column name     First  copy main table row  ColumnHdr      Query  SHOW COLUMNS FROM  main table      Result Wrappedmysql query  Query  link   FILE     LINE     while  Row mysql fetch array  Result         if  Row  Field     MainTableID         skip main table id in column list         continue       ColumnHdr          Row  Field             Query  INSERT INTO  main table       substr  ColumnHdr 1                SELECT     substr  ColumnHdr 1      FROM  main table              WHERE  MainTableID       OldMainTableID          Result Wrappedmysql query  Query  link   FILE     LINE      NewMainTableID mysql insert id  link      Change the name  assumes a 30 char field   Query  UPDATE  main table  SET  Title  CONCAT SUBSTRING  Title  1 25    Copy   WHERE  MainTableID       NewMainTableID         Result Wrappedmysql query  Query  link   FILE     LINE        now copy in the linked tables  TableArr array  main table link1   main table link2   main table link3    foreach  TableArr as  TableArrK  gt  TableArrV         ColumnHdr          Query  SHOW COLUMNS FROM       TableArrV              Result Wrappedmysql query  Query  link   FILE     LINE         while  Row mysql fetch array  Result                 if  Row  Field     MainTableID         skip main table id in column list  re-added in query             continue          if  Row  Field     dbID        skip auto-increment primary key in linked table             continue           ColumnHdr          Row  Field                      Query  INSERT INTO       TableArrV        MainTableID      substr  ColumnHdr 1                    SELECT      NewMainTableID         substr  ColumnHdr 1      FROM       TableArrV                   WHERE  MainTableID       OldMainTableID              Result Wrappedmysql query  Query  link   FILE     LINE

User · Answer

This is an additional solution to the answer by  Grim     There have been some comments on it having a primary key as null  Some comments about it not working  And some comments on solutions  None of the solutions worked for us  We have MariaDB with the InnoDB table   We could not set the primary key to allow null  Using 0 instead of NULL led to duplicate value error for the primary key  SET SQL SAFE UPDATES   0  Did not work either   The solution from  Grim     did work IF we changed our PRIMARY KEY to UNIQUE instead

User · Answer

If the Primary Key is Auto Increment  just specify each field except the primary key   INSERT INTO table field1 field2 field3    SELECT  field1 field2 field3  FROM table  WHERE primarykey 1

[mysql] In MySQL, can I copy one row to insert into the same table?

Examples related to mysql

Examples related to copy

Examples related to duplicates

Examples related to row