Mysql Clone Row With Primary Key

Question

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 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...

Answer 1

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;

Answer 2

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'