In MySQL, can I copy one row to insert into the sa

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

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 2

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 3

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 4

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 5

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 6

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;