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 youIdentity 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.|||Im just qurious..
Why do you want to change it?|||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 ...|||don't do it

see Gaps in autonumber sequences (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid576584_tax285649,00.html)
(registration may be required, but it's free)

just leave the gaps alone|||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|||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.|||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?|||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

;)|||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?|||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.|||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