CODEWITHSUNDEEP
sqlsql-servert-sql
lowerkey
lowerkey

Reputation: 8325

Update values in identity column

How do I override the identity column in MSSQL? I tried :

    SET IDENTITY_INSERT GeoCountry ON
    UPDATE GeoCountry SET CountryID = 18 WHERE CountryID = 250

But I get back a

Line 2: Cannot update identity column 'CountryID'.

Upvotes: 36

Views: 117999

Answers (5)

Ismail Hawayel
Ismail Hawayel

Reputation: 2443

You could also do this in one statement using delete into, this has the benefit of eliminating any error copying/moving the row data, for example

set identity_insert [dbo].[MyTableName] on

delete from [dbo].[MyTableName]
output 
<new-id-value-here>,
[deleted].[Col1], 
[deleted].[Col2], 
[deleted].[Col3], 
into 
[dbo].[MyTableName] (
[IdColumnName], 
[Col1], 
[Col2], 
[Col3])
where
[IdColumnName]=<old-id-value-here>

set identity_insert [dbo].[MyTableName] off

Upvotes: 6

Ramon Baiges Miro
Ramon Baiges Miro

Reputation: 21

If you want to reenumerate the values of an identity field, because for instance the values have gone mad, just do as follows:

  • open table in design mode click on Identity Specification

    mark (is identity) as NO (Your identity field is still a PK)

    close and save design

  • open table in edit mode Change the values of your Identity field as desired (just be aware you cannot have duplicate values)

  • Close the table and open it again in design mode Replace your identity field (is identity) to Yes.

    Close the table and you are done.

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272076

If you are trying to update an identity column here is one possible approach:

  • In SQL Server Management Studio, open the table in design view, disable "Identity Specification > Is Identity" on the column
  • Perform updates
  • Enable "Identity Specification > Is Identity" on the column

Do a SELECT IDENT_CURRENT('<table name>') to see if it returns the highest id that is currently present in the table.

Upvotes: 10

Mitch Wheat
Mitch Wheat

Reputation: 300529

You are trying to perform an update, not inserting new rows.

In order to do that, you will need to set identity_insert ON and copy the row you want to update to a new row with the new ID value, then delete the old row (assuming no FK is referencing it)

Something along the lines of:

set identity_insert GeoCountry on
go

insert into GeoCountry (all columns including IDentity column) 
     select 18, (all columns except IDentity column)
     from GeoCountry where CountryID = 250 

-- Delete will only work if no referencing FK's
delete GeoCountry where CountryID = 250

set identity_insert GeoCountry off
go

[Given that you are trying to update it, that would suggest it is still in use (i.e. by referencing FK's) and that makes things more complicated...]

Upvotes: 70

Gary J.
Gary J.

Reputation: 137

You cannot update the Identity Column in SQL Server. You have to delete the original record, then Insert the record with the Identity value because there is no support for updating an identity value.

set Identity_Insert [ColumnName] On Insert identity and additional information previously stored in that record set Identity_Insert [ColumnName] Off

Upvotes: 12

Related Questions