# mysql 4.0.18 syntax for duplicating a row



## snorkytheweasel (May 3, 2006)

I'm trying to clone a row in a mysql table so that there will be 2 entries which are identical except for the value of the primary key. The primary key is designed to auto_increment.I will 'manually' change - by editing the contents of 1 non-key column - the handful of added rows.

I have tried several variations on the following:
The table name is "data." 730 is a value which is not already used in the auto_increment primary key.

mysql > insert into data 
select id,fname,lname,title,subject,bldg,room,phone,email,emailspec,cell,editdate,accessed,fax,pager,notes1,notes2,notes3,notes4,deleted from data where id="121" on duplicate_key update id=''730';

mysql > insert into data 
select fname,lname,title,subject,bldg,room,phone,email,emailspec,cell,editdate,accessed,fax,pager,notes1,notes2,notes3,notes4,deleted from data where id="121" on duplicate_key update id=''730';

mysql > insert into data 
select id,fname,lname,title,subject,bldg,room,phone,email,emailspec,cell,editdate,accessed,fax,pager,notes1,notes2,notes3,notes4,deleted from data where id="121" on duplicate_key auto_increment id;

mysql > select fname,lname,title,subject,bldg,room,phone,email,emailspec,cell,editdate,accessed,fax,pager,notes1,notes2,notes3,notes4,deleted from data where id="121" on duplicate_key auto_increment id;


----------



## Chicon (Jul 29, 2004)

Hi snorkytheweasel,

If you're using *INSERT ... ON DUPLICATE KEY UPDATE*, there will be no insertion of a new row if there's a duplicate key. Instead, the old row will be updated.
(see *INSERT ... ON DUPLICATE KEY UPDATE Syntax*).

If you want to add a new row with a different key, as _id_ is auto-increment, therefore you have just to write the _INSERT_ statement with your _SELECT_ clause ignoring _id_ :
*INSERT INTO data VALUE ( fname,lname,title,subject,bldg,room,phone,email,emailspec,cell,editdate,
accessed,fax,pager,notes1,notes2,notes3,notes4,deleted)
SELECT fname,lname,title,subject,bldg,room,phone,email,emailspec,cell,editdate, accessed,fax,pager,notes1,notes2,notes3,notes4,deleted
FROM data
WHERE id = 121*
Notice that _id_ is not present in the _VALUE_ part and after the _SELECT_ word, the system will generate automatically a new value for _id_.


----------

