sql - Update values in identity column

Question

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'.

Answer 1

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...]