Can an IDENTITY column be updated?

(SQL Server 2000, SP3)
Hello all!
I have a simple table:
create table Test (Id int identity(1, 1) not NULL, Name varchar(255) NULL)
insert into Test (Name) values ('Test')
And I'd like to potentially update the IDENTITY column Id:
update Test set Id = 100 where Id = 1
However, I get the following error:
Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'Id'.
Even if I try to "wrap" the UPDATE in a "set identity_insert", I still get the same error.
Is there any way to update a column with an IDENTITY property?
Thanks!
John PetersonThanks Sue! Ah, I see the blurb in BOL that says an IDENTITY can't be updated. Bummer.
:-(
It seems to me that in older versions of SQL Server, one could update a column with the
IDENTITY property. But, no longer (or my memory isn't what it once was ;-).
Thanks again!
John Peterson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:dc1njvsscenku5aoklp93tl6rjptcibdcf@.4ax.com...
> Identity columns can't be updated - I think it's documented
> under the UPDATE topic in BOL T-SQL reference. One possible
> option would be to set identity_insert on, use the existing
> values for a new record and insert the new record with the
> identity value you need to use and then delete the old
> record.
> -Sue
> On Wed, 13 Aug 2003 20:10:46 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >(SQL Server 2000, SP3)
> >
> >Hello all!
> >
> >I have a simple table:
> >
> >create table Test (Id int identity(1, 1) not NULL, Name varchar(255) NULL)
> >insert into Test (Name) values ('Test')
> >
> >And I'd like to potentially update the IDENTITY column Id:
> >
> >update Test set Id = 100 where Id = 1
> >
> >However, I get the following error:
> >
> >Server: Msg 8102, Level 16, State 1, Line 1
> >Cannot update identity column 'Id'.
> >
> >Even if I try to "wrap" the UPDATE in a "set identity_insert", I still get the same
error.
> >
> >Is there any way to update a column with an IDENTITY property?
> >
> >Thanks!
> >
> >John Peterson
> >
>