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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

You could also try dumping the table, finding the insert command and editing it:

mysqldump -umyuser -p mydatabase --skip-extended-insert mytable > 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".

Answer 6

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.

Answer 7

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;

Answer 8

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.

Answer 9

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;

Answer 10

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`;

Answer 11

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';

Answer 12

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 < $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<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
}

Answer 13

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

Answer 14

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

Answer 15

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.

Answer 16

I would use below,

insert into ORDER_ITEM select * from ORDER_ITEM where ITEM_NUMBER =123;

Answer 17

I just had to do this and this was my manual solution:

1. In phpmyadmin, check the row you wish to copy
2. At the bottom under query result operations click 'Export'
3. On the next page check 'Save as file' then click 'Go'
4. Open the exported file with a text editor, find the value of the primary field and change it to something unique.
5. 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.

Answer 18

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;";

Answer 19

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

Answer 20

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=>$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__);
}

Answer 21

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.

Answer 22

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->prepare('SHOW COLUMNS FROM template');
  $stmt->execute();

  $columns = $stmt->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->prepare($sql);
  $stmt->execute();

Answer 23

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`;

Answer 24

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.

Answer 25

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

Answer 26

Drawing from answers above, and elsewhere on SO, I came to this as my final code for cloning one record:

CREATE TEMPORARY TABLE temptable SELECT * FROM things WHERE Thing_ID = 10000345;
UPDATE temptable SET Thing_ID = 0;
INSERT INTO things SELECT * FROM temptable;
DROP TEMPORARY TABLE IF EXISTS temptable;

Setting the value of the PRIMARY key, the Thing_ID, to be = 0 is shorter than altering the temptable to allow NULL and then setting the PRIMARY key to NULL - and, for me, it works in MySQL.