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?
To select duplicate values, you need to create groups of rows with the same values and then select the groups with counts greater than one. You can achieve that by using GROUP BY and a HAVING clause.
When inserting a single row into the MySQL table, the syntax is as follows: INSERT INTO table_name(column_1,column_2,column_3) VALUES (value_1,value_2,value_3); In the INSERT INTO query, you should specify the following information: table_name : A MySQL table to which you want to add a new row.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With