Mysql Clone Row with primary key

Question

I have a Mysql table with one primary key (called pkey) that grows automatically, and I would like to clone one row, keeping all the data the same, except for the primary key, which should become the next available value as determined by auto-increment.

My first question is: is the following query possible?

UPDATE `table` SET pkey='next_available_primary_key' WHERE pkey='old_primary_key' 

if you tried

 UPDATE `table` SET pkey=null WHERE pkey='old_primary_key' 

But it only sets the primary key value to zero. Thank you in advance for any help / suggestions.

UPDATE:

I think I should add that I really don't need two copies of the data in the table. I just want to change the primary key. So if I used INSERT SELECT, I would have to compensate for using ON DUPLICATE KEY UPDATE pkey = 'next_available_primary_key', I'm just not sure how to do this ...

Answer 1

You want INSERT, not UPDATE if you are trying to create a new row in a table.

How about this? Make sure your PKEY is set to auto-increment.

 INSERT INTO `table` (col,col,col) /*name all the columns EXCEPT the primary key*/ SELECT col,col,col /*name all the columns EXCEPT the primary key*/ FROM 'table` WHERE pkey='old_primary_key' 

Answer 2

 insert into t select 0,a,b,c,d,e from t where id = some_id 

use 0 as the value for the auto_increment column, mysql will use the following available ...

edited for your new comment, if you want to change the identifier to the next available one,

 update tbl set id = (select auto_increment from information_schema.tables where table_name = 'tbl') where id = 4; 

Answer 3

What about the solution found on clone-sql-record ?

 CREATE TEMPORARY TABLE %1 ENGINE=MEMORY SELECT * FROM mytable WHERE myid=%2; UPDATE %1 SET myid=%3; INSERT INTO mytable SELECT * FROM %1; DROP TABLE %1; 

Where

• % 1 - "T" + raw_time_stamp, ex T20120124132754
• % 2 is old
• % 3 - new

Answer 4

Cloning table entries with an automatic primary key

 CREATE TEMPORARY TABLE `tmp` SELECT * FROM `your_table_name`; UPDATE `tmp` SET id = NULL ; INSERT INTO `your_table_name` SELECT * FROM `tmp`; DROP TEMPORARY TABLE IF EXISTS `tmp`; 

Answer 5

Note that the other solutions provided seem to work on the source table, which allows zero for the primary key column. Since this does not apply to me, I encountered an error that can be easily solved by changing the temporary table (I expected id be the main one):

 CREATE TEMPORARY TABLE tmp_clone ENGINE=MEMORY SELECT * FROM my_source_table WHERE id=some_id; ALTER TABLE tmp_clone MODIFY id bigint(20) default null; UPDATE tmp_clone SET id=null; INSERT INTO my_source_table SELECT * FROM tmp_clone; DROP TABLE tmp_clone; SELECT * from my_source_table where id = last_insert_id();