Professional Web Applications Themes

duplicate a row on a table with autoincrement - MySQL

Greetings, This seems like a simple task: insert into prizes select * from prizes where prize_id=16; but it gives an error because the prize_id column is autoincrement. this fails as well insert into prizes select * from prizes where prize_id=16 ON DUPLICATE KEY UPDATE prize_id=(select MAX(prize_id)+1 from prizes); strangely changing the prize_id from 16 to 17 rather than inserting 17. I can create a temporary table with a single row, alter the prize_id column in that table and insert the row back. I have also seen solutions where the autoincrement is disabled and reenabled using 'modify table' commands, but that ...

  1. #1

    Default duplicate a row on a table with autoincrement

    Greetings,
    This seems like a simple task:
    insert into prizes select * from prizes where prize_id=16;
    but it gives an error because the prize_id column is autoincrement.
    this fails as well
    insert into prizes select * from prizes where prize_id=16 ON DUPLICATE
    KEY UPDATE prize_id=(select MAX(prize_id)+1 from prizes);
    strangely changing the prize_id from 16 to 17 rather than inserting
    17.

    I can create a temporary table with a single row, alter the prize_id
    column in that table and insert the row back. I have also seen
    solutions where the autoincrement is disabled and reenabled using
    'modify table' commands, but that could get ugly IMHO.

    In reality it takes 4 steps to accomplish my goal because I can alter
    the data I want to change on the new row in the same command as
    altering the prize_id. Optimally it would take 3 (duplicate, get
    last_insert_id(), and update), and performance isn't an issue with
    this task, but it bugs me to be so round-about.

    suggestions?
    -- clh

    christopher@dailycrossword.com Guest

  2. #2

    Default Re: duplicate a row on a table with autoincrement

    >insert into prizes select * from prizes where prize_id=16; 

    Why is this strange? You've got a duplicate key (prize_id).

    How about this, assuming that your prizes table has fields prize_id,
    nerk, jello, and noodle?

    insert into prizes (prize_id, nerk, jello, noodle)
    select null, nerk, jello, noodle from prizes where prize_id = 16;


    Gordon Guest

  3. #3

    Default Re: duplicate a row on a table with autoincrement

    Gordon Burditt wrote: 
    >
    > Why is this strange? You've got a duplicate key (prize_id).
    >
    > How about this, assuming that your prizes table has fields prize_id,
    > nerk, jello, and noodle?
    >
    > insert into prizes (prize_id, nerk, jello, noodle)
    > select null, nerk, jello, noodle from prizes where prize_id = 16;
    >
    >[/ref]

    Or, just leave the prize_id column out all together:

    insert into prizes (nerk, jello, noodle)
    select nerk, jello, noodle from prizes where prize_id = 16;

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  4. #4

    Default Re: duplicate a row on a table with autoincrement

    On May 19, 5:48 pm, org (Gordon Burditt) wrote: 
    >
    > Why is this strange? You've got a duplicate key (prize_id).[/ref]

    it is strange because the insert is never done. the docs read like
    the "on duplicate key update" updates the inserted row to avoid the
    error which is what I expected.

     

    I need a generic solution so the code does not need to be altered when
    the table changes.




    christopher@dailycrossword.com Guest

  5. #5

    Default Re: duplicate a row on a table with autoincrement

    >> >insert into prizes select * from prizes where prize_id=16; 
    >>
    >> Why is this strange? You've got a duplicate key (prize_id).[/ref]
    >
    >it is strange because the insert is never done.[/ref]

    If you have a duplicate key, the insert is not done. This is what
    unique indexes are for - preventing the insertion (or other creation)
    of duplicates.
     

    No, the update acts on the CONFLICTING row. There is no inserted
    row in this situation. It is unclear what will happen if there are
    multiple conflicting rows because there are multiple unique keys
    and multiple conflicts in the inserted records. I assume it would
    pick one of the conflicting rows, but I don't know which. Or it
    might do all of them.

    One use for this (there's a unique index on url, not count):

    insert into hit_counter(url, count) values($url, 1)
    on duplicate key update count=count+1;

    This lets you do a "fire and forget" query to increment a counter whether
    a record for the given URL previously exists or not.
     
    >
    >I need a generic solution so the code does not need to be altered when
    >the table changes.[/ref]

    I don't think you're going to get one that doesn't involve constructing
    SQL on the fly after getting a list of field names.

    Gordon Guest

  6. #6

    Default Re: duplicate a row on a table with autoincrement

     

    thanx --
    I think my misunderstanding was because I was expecting the docs to he
    helpful hehe
    there are two entries that reference "ON DUPLICATE":
    http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
    and
    http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
    of course I was using the first one and only the second one specifies
    the command acts like an "UPDATE".
    Sigh.
     

    the second entry in the docs addresses this situation, if you are
    interested
     

    Yeah -- that's more complex than creating the temporary table,
    changing the autoincrement column, and inserting it back.

    Thanx for your input!

    christopher@dailycrossword.com Guest

Similar Threads

  1. SHOW TABLE STATUS & Autoincrement???
    By soup_or_power@yahoo.com in forum MySQL
    Replies: 3
    Last Post: May 16th, 05:06 PM
  2. Duplicate a record in a table from a Form.
    By Jamie Perry in forum Microsoft Access
    Replies: 4
    Last Post: August 7th, 08:52 PM
  3. Finding Duplicate Values in a table
    By Dan in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 17th, 03:30 PM
  4. ViewState + AutoIncrement -- Okay; Session + AutoIncrement -- Not okay
    By Gene Gorokhovsky in forum ASP.NET General
    Replies: 0
    Last Post: July 17th, 02:48 PM
  5. How to find duplicate records in the table?
    By mac in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 7th, 07:52 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139