0

I have a column called fixedID, and it's current Value = 2, I want to update this value to be 42. What is the best way to do this. I would like to do this modification as simply as possible. However, if I could do this while doing an select insert that would be fantastic also.

update tblFixedId
set FixedId = (FixedId + 400)

possible to change the value here?

Select * INTO mynewTable from myOldertable
  • Your code sample is at odds with the question body. It would set `FixedId` to 402 (assuming it is 2), not to 42. Can you please clarify if you want to update the whole table, just one row and what rule you want to use for updating? – Oded Dec 23 '11 at 16:50
  • Please explain what `tblFixedId`, `mynewTable`, `myOldertable` all are? – Martin Smith Dec 23 '11 at 17:04

1 Answers1

0

Identity columns are not updatable in SQL Server.

The only way of doing this as an actual UPDATE rather than a DELETE ... INSERT would be to use ALTER TABLE ... SWITCH to mark the column as no longer an IDENTITY column, do the UPDATE then ALTER TABLE ... SWITCH again to re-mark the column as IDENTITY (For example code the first way round see the workarounds on this Connect Item and for the second way here).

Note that in the common scenario that the identity column is the clustered index key the Update will likely be implemented as an INSERT ... DELETE anyway.

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Or, insert a new record with the desired ID, copy the fields and then delete the original record. All within a transaction of course. – Steve Wellens Dec 23 '11 at 16:55
  • @Oded - What about it? It is quicker to just create the table with the desired new `identity` value as per [my example here](http://stackoverflow.com/a/6086661/73226) – Martin Smith Dec 23 '11 at 16:57