how do i copy one row to insert into the same table?

Question

insert into table select * from table where primarykey=1

I just wanna copy one row to insert into the same table, but i don't want list all the columns after the "select",cause 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 same columns as the 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;

Can there be some more way to solve this more simply?

Answer 1

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.

Answer 2

You could try using:

INSERT INTO table 
SELECT * FROM table ON DUPLICATE KEY UPDATE key = key + 1

...but if the column is either the primary key (or has a unique constraint on it) and there is already a value that equals key + 1 it won't work.

If the key column is auto_increment, you would need to list the columns but use either DEFAULT or NULL for the key value:

INSERT INTO table
SELECT DEFAULT, col1, col2, col3 
  FROM TABLE

...in order to appease the constraint.

If you're using MySQL WorkBench, right click on the table -- one of the options will generate either INSERT, UPDATE, DELETE or SELECT statements (including all columns) so you don't have to type so much.