use next auto_increment id with ON DUPLICATE KEY?

[mitya]

Hi guys

Sure this must be simple, just not sure how to do it.

When replicating a row, how can I set the ON DUPLICATE KEY clause to update the PK with the next auto_increment id? I tried

insert into table select * from users where id = 1 on duplicate key update (select max(id)+1 from table)

...but it didn't like it.

Thanks in advance

[tirna]

say you have a table with columns

fldPersonID auto_incrementing primary key
fldFamilyName
fldGivenName

then to duplicate the row with fldPersonID = 5 use:

 
insert into tblperson (fldFamilyName,fldGivenName)
select t.fldFamilyName,t.fldGivenName 
from tblperson t
where t.fldPersonID = 5

and the newly inserted row will be allocated the next auto_incrementing value for fldPersonID.

[mitya]

Thanks, I'll give it a go

[tirna]

no problem.