CODEWITHSUNDEEP
mysqlclonerow
John Space
John Space

Reputation: 41

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

Upvotes: 4

Views: 5239

Answers (5)

s10z
s10z

Reputation: 1130

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

Upvotes: 0

Anam Shah
Anam Shah

Reputation: 319

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`;

Upvotes: 0

Paul Crown
Paul Crown

Reputation: 31

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

Upvotes: 3

O. Jones
O. Jones

Reputation: 108839

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'

Upvotes: 7

jspcal
jspcal

Reputation: 51944

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;

Upvotes: 3

Related Questions