Results 1 to 12 of 12
  1. #1
    Join Date
    May 2003
    Posts
    11

    Unanswered: change value in a identity column

    How can I change the value in a identity column? I cannot use update to change its value.

    Thank you

  2. #2
    Join Date
    Dec 2003
    Posts
    17
    Identity columns can not be updated but you could get around this by doing the following:

    --Allows you to enter your own value into the Identity column in
    --your table
    SET IDENTITY_INSERT [table name] ON
    INSERT [table name] ([enter full column list here])
    SELECT [New ID], [all other columns]
    FROM [table name]
    WHERE ID = [Old ID]
    SET IDENTITY_INSERT [table name] OFF

    DELETE
    FROM [table name]
    WHERE ID = [Old ID]

    You might also want to put all this inside a transaction to ensure everything happens as you expect it too.

  3. #3
    Join Date
    Feb 2002
    Location
    Sweden
    Posts
    34
    Im just qurious..
    Why do you want to change it?
    - Jonte

  4. #4
    Join Date
    May 2003
    Posts
    11
    because i want to remove some old data in a identity enabled table, and i want to change the remind data's identity value back to starting from 1 to ...

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    don't do it

    see Gaps in autonumber sequences
    (registration may be required, but it's free)

    just leave the gaps alone
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2004
    Posts
    28
    what he said.

    for whatever reason you think you need them sequential you need to rethink.

    If you'd like, post your reasons to this list and folks can help you find better ways to do what you are after.....


    Originally posted by r937
    don't do it

    .....

    just leave the gaps alone
    just an analyst......

  7. #7
    Join Date
    May 2003
    Posts
    11
    I have a table which keeps users' saved exams, and it increases fast. I need to remove anything which are 3 months old with a schedule job which will run once a week. Exam ID is the identity field. I dont want to let the Exam ID grow to too big value. So after I removed the old exams, I want to reset the exams ID in that table back to starting with 1.
    And that is my case. Thanks for all suggestions.

  8. #8
    Join Date
    Feb 2004
    Posts
    492
    Originally posted by cobraeyez
    I have a table which keeps users' saved exams, and it increases fast. I need to remove anything which are 3 months old with a schedule job which will run once a week. Exam ID is the identity field. I dont want to let the Exam ID grow to too big value. So after I removed the old exams, I want to reset the exams ID in that table back to starting with 1.
    And that is my case. Thanks for all suggestions.
    How many exams are taken a day? Consider BOL: an integer can have a value upto 2,147,483,647, a bigint upto 9,223,372,036,854,775,807. Is it really necessary to reset the identity field?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Originally posted by cobraeyez
    I dont want to let the Exam ID grow to too big value.
    define "too big"

    as Kaiowas suggested, it will be a long time before you run out of numbers

    for example, if you add 1,000 new exams every day, guess how long you can keep adding without having to worry about it?

    2,147,483,647 / 1000 = 2,147,487 days = 5879.5 years

    again, please define "too big" and why it's too big


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Posts
    492
    Just thought of another reason NOT to do this:
    - The GAP indicates there used to be more exams;
    - What would you do should you have have to readback from an archive?

  11. #11
    Join Date
    Mar 2004
    Posts
    28
    Why do you want to remove more then 3 months?

    Why don't you want a large exam id? Is this something you show to the users?

    I'd argue that users are used to large numbers they need to write down. I'd also argue that if Fred took a test 9 months ago, and he wanted information about it, you should be able to find it.

    Big is a relative term. If you get more then 10,000,000 rows you need to look at your indexes again. Do you give 10,000,000 exams in a 3 month period?

    Phone numbers are this long. People can remember them if they need to.

    Originally posted by cobraeyez
    I have a table which keeps users' saved exams, and it increases fast. I need to remove anything which are 3 months old with a schedule job which will run once a week. Exam ID is the identity field. I dont want to let the Exam ID grow to too big value. So after I removed the old exams, I want to reset the exams ID in that table back to starting with 1.
    And that is my case. Thanks for all suggestions.
    just an analyst......

  12. #12
    Join Date
    May 2003
    Posts
    11
    well, it seems I really don't need to. The original reason was I was using integer type in VB to retrieve the exam ID. Now I changed it to Long. It should be ok.

    Thank for all your help
    Last edited by cobraeyez; 03-16-04 at 12:33.

Posting Permissions

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