identity insert issue

I am trying to update an identity column with a new value. I am doing the
following:
set identity_insert tbgfmla4.dbo.[tblname] on
go
update [tblname]
set identitycolumn= 124926
where [fieldname] = 'ABCD'
and it gives me an error
Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'identitycolumn'.
What am I doing wrong? Thanks in advance."sharman" <sharman@.discussions.microsoft.com> wrote in message
news:8E0F1ECE-30E1-4780-B5A0-E89BCBE8ABF9@.microsoft.com...
>I am trying to update an identity column with a new value. I am doing the
> following:
> set identity_insert tbgfmla4.dbo.[tblname] on
> go
> update [tblname]
> set identitycolumn= 124926
> where [fieldname] = 'ABCD'
> and it gives me an error
> Server: Msg 8102, Level 16, State 1, Line 1
> Cannot update identity column 'identitycolumn'.
> What am I doing wrong? Thanks in advance.
An IDENTITY column cannot be updated under any circumstances, irrespective
of the IDENTITY_INSERT setting. If this is a problem for you then don't use
IDENTITY.
You can however DELETE and the re-INSERT the IDENTITY value if
IDENTITY_INSERT is on, assuming you avoid violating any constraints by doing
so.
--
David Portas|||On Nov 27, 3:37 am, sharman <shar...@.discussions.microsoft.com> wrote:
> I am trying to update an identity column with a new value. I am doing the
> following:
> set identity_insert tbgfmla4.dbo.[tblname] on
> go
> update [tblname]
> set identitycolumn= 124926
> where [fieldname] = 'ABCD'
> and it gives me an error
> Server: Msg 8102, Level 16, State 1, Line 1
> Cannot update identity column 'identitycolumn'.
> What am I doing wrong? Thanks in advance.
Note that with set identity_insert tbgfmla4.dbo.[tblname] on, you can
only add value to the column and you cant update it
Why do you want to update identity column?