SQL 更新标识列中的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3947453/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update values in identity column
提问by lowerkey
How do I override the identitycolumn in MSSQL? I tried :
如何覆盖identityMSSQL 中的列？我试过 ：
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'.
第 2 行：无法更新身份列“CountryID”。
回答by Mitch Wheat
You are trying to perform an update, not inserting new rows.
您正在尝试执行更新，而不是插入新行。
In order to do that, you will need to set identity_insertON andcopy 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)
为此，您需要设置identity_insertON并将要更新的行复制到具有新 ID 值的新行，然后删除旧行（假设没有 FK 引用它）
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...]
[鉴于您正在尝试更新它，这表明它仍在使用中（即通过引用 FK 的方式），这使事情变得更加复杂...]
回答by Gary J.
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.
您无法更新 SQL Server 中的标识列。您必须删除原始记录，然后插入具有标识值的记录，因为不支持更新标识值。
set Identity_Insert [ColumnName] On Insert identity and additional information previously stored in that record set Identity_Insert [ColumnName] Off
set Identity_Insert [ColumnName] On 插入先前存储在该记录集中的标识和附加信息 Identity_Insert [ColumnName] Off
回答by Salman A
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
- 在 SQL Server Management Studio 中，在设计视图中打开表，在列上禁用“身份规范 > 是身份”
- 执行更新
- 在列上启用“身份规范> 是身份”
Do a SELECT IDENT_CURRENT('<table name>')to see if it returns the highest id that is currently present in the table.
执行 aSELECT IDENT_CURRENT('<table name>')以查看它是否返回表中当前存在的最高 id。
回答by Ismail Hawayel
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
您也可以使用 delete into 在一个语句中执行此操作，这有利于消除复制/移动行数据的任何错误，例如
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
回答by Ramon Baiges
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.
在设计模式下打开表格单击身份规范
将（身份）标记为 NO（您的身份字段仍然是 PK）
关闭并保存设计
在编辑模式下打开表根据需要更改身份字段的值（请注意您不能有重复的值）
关闭表并在设计模式下再次打开它 将您的身份字段（是身份）替换为是。
关闭桌子，你就完成了。
