www.webdeveloper.com
Results 1 to 4 of 4

Thread: use next auto_increment id with ON DUPLICATE KEY?

  1. #1
    Join Date
    Oct 2003
    Location
    Andover, UK
    Posts
    945

    use next auto_increment id with ON DUPLICATE KEY?

    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

    Code:
    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

  2. #2
    Join Date
    Mar 2010
    Posts
    2,803
    say you have a table with columns

    fldPersonID auto_incrementing primary key
    fldFamilyName
    fldGivenName

    then to duplicate the row with fldPersonID = 5 use:

    Code:
     
    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.
    Last edited by tirna; 04-15-2010 at 05:29 AM.

  3. #3
    Join Date
    Oct 2003
    Location
    Andover, UK
    Posts
    945
    Thanks, I'll give it a go

  4. #4
    Join Date
    Mar 2010
    Posts
    2,803
    no problem.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles