Mysql Clone Row With Primary Key

I have a Mysql table with a single primary key (called pkey) that auto increments, 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 defined 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 have tried

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

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

UPDATE:

I guess i should add that i don't really want two copies开发者_如何学JAVA of the data in the table. I just want to change the primary key. So if i were to use INSERT SELECT i would have to compensate using ON DUPLICATE KEY UPDATE pkey='next_available_primary_key' i am just not sure how to do this...

You want INSERT, not UPDATE, if you're trying to make a new row in the table.

How about this? Make sure your PKEY is set to autoincrement.

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'

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 next available one...

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

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

How 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 is "T"+raw_time_stamp, ex T20120124132754
• %2 is oldid
• %3 is newid

Clone records of table, with autoincremented 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`;

Note that the other solutions given seem to work on a source table that allows null for the primary key column. As this is not the case for me I ran into an error -- which can be easily solved by altering the temporary table (I expected id to be the primary key):

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();