use next auto_increment id with ON DUPLICATE KEY?

This site is best viewed in a modern browser with JavaScript enabled.

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

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.

Thanks, I'll give it a go

no problem.