更新标识列中的值 [英] Update values in identity column

问题描述

如何覆盖 MSSQL 中的 identity 列?我试过了:

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

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

但我回来了

第 2 行:无法更新身份列CountryID".

Line 2: Cannot update identity column 'CountryID'.

推荐答案

您正在尝试执行更新，而不是插入新行.

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

为了做到这一点，您需要将 identity_insert 设置为 ON 并将要更新的行复制到具有新 ID 值的新行，然后删除旧行(假设没有 FK 引用它)

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)

大致如下:

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

[鉴于您正在尝试更新它，这表明它仍在使用中(即通过引用 FK 的方式)，这让事情变得更加复杂...]

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

这篇关于更新标识列中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！