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

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?

 Answers

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.

---

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 f

or 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.

---

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;

---

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
}

---

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

---

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.

---

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

---

I would use below,

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

---

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

---

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

---

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 no value before you import it.

SELECT * FROM table where primarykey=1

Then at the bottom of the page:

Where is says "Export" 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.

---

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();