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

user_489657 0
Viewed 259911
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?

row duplicates copy mysql
26 Answers

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_114031840

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_30670830

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_63140790

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.

user_14161040
Related