[mysql] 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?

This question is related to mysql copy duplicates row

The answer is


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.


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.


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
}

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

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;

I would use below,

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

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.


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.


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

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.


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;

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.


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

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:

enter image description here

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.


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.


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


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.


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


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.


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

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

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

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


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


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


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to copy

Copying files to a container with Docker Compose Copy filtered data to another sheet using VBA Copy output of a JavaScript variable to the clipboard Dockerfile copy keep subdirectory structure Using a batch to copy from network drive to C: or D: drive Copying HTML code in Google Chrome's inspect element What is the difference between `sorted(list)` vs `list.sort()`? How to export all data from table to an insertable sql format? scp copy directory to another server with private key auth How to properly -filter multiple strings in a PowerShell copy script

Examples related to duplicates

Remove duplicates from dataframe, based on two columns A,B, keeping row with max value in another column C Remove duplicates from a dataframe in PySpark How to "select distinct" across multiple data frame columns in pandas? How to find duplicate records in PostgreSQL Drop all duplicate rows across multiple columns in Python Pandas Left Join without duplicate rows from left table Finding duplicate integers in an array and display how many times they occurred How do I use SELECT GROUP BY in DataTable.Select(Expression)? How to delete duplicate rows in SQL Server? Python copy files to a new directory and rename if file name already exists

Examples related to row

Python Pandas Replacing Header with Top Row Return row number(s) for a particular value in a column in a dataframe How to insert a row in an HTML table body in JavaScript Referencing Row Number in R Python pandas: fill a dataframe row by row MS SQL 2008 - get all table names and their row counts in a DB How do I delete rows in a data frame? Remove table row after clicking table row delete button Javascript get the text value of a column from a particular row of an html table How to add a new row to datagridview programmatically