updating identity column

[sparkme]

Can anyone tell me how to update identity column

example id is identity colummn in author table i want to

update author
set id = new value
where id = old value

thanks

 

[bborissov]

WHY?

But if you insist:

SET IDENTITY_INSERT TABLE author ON
update author
set id = new value
where id = old value
SET IDENTITY_INSERT TABLE author OFF

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[sparkme]

NO SET IDENTITY_INSERT TABLE author ON WILL WORK FOR INSERT BUT NOT FOR UPDATE

 

[SQLDenis]

you can't do that without removing the identity property first

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[bborissov]

Sorry [red face] my mistake

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

Hey Borislav you could have used this one

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLSister]

SInce no one has talked to you about why this is a bad idea, maybe I should. The identitiy field is the key field that other tables use to link to the information in most cases. If you cahnge it and you do not have cascading update enabled then you lose the integrity of your data. data in child tables will no longer match to the correct data. This is a bad thing and too be avoided if at all possible. If you tell us why you want to change the idneity field maybe we can point you to a less dangerous solution. Whatever you do, do not change the identity field without taking a full backup of the table first. It's better to spend the time to do this than to lose your job because your data is hopelessly and irretrievably messed up.

Questions about posting. See faq183-874