将您的表复制到没有标识列的新表。

    select columns into newtable from yourtable

用new seed添加一个标识列到newtable，并将其作为主键

    ALTER TABLE tableName ADD id MEDIUMINT NOT NULL AUTO_INCREMENT KEY

如果你的问题答对了，你想做的是

update table
set identity_column_name = some value

让我告诉你，这不是一个简单的过程，使用它是不可取的，因为它可能有一些相关的外键。

但这里有一些步骤，请采取备份表

步骤1-选择表的设计视图

步骤2-关闭标识列

现在可以使用更新查询了。

现在重做第1步和第2步，并打开标识列

参考

ALTER TABLE tablename add newcolumn int
update tablename set newcolumn=existingcolumnname
ALTER TABLE tablename DROP COLUMN existingcolumnname;
EXEC sp_RENAME 'tablename.oldcolumn' , 'newcolumnname', 'COLUMN'
update tablename set newcolumnname=value where condition

然而，上面的代码只有在没有主-外键关系的情况下才能工作

您还可以使用SET IDENTITY INSERT来允许您将值插入到标识列中。

例子:

SET IDENTITY_INSERT dbo.Tool ON
GO

然后你可以在单位列中插入你需要的值。

我做了以下事情:

将相关数据移动到临时存储中 更新主键/标识列值(删除和创建约束) 用新的外键值重新插入相关数据

我把我的解决方案包装在一个存储过程中:

CREATE PROCEDURE [dbo].[UpdateCustomerLocationId]
    @oldCustomerLocationId INT,
    @newCustomerLocationId INT
AS
  /* 
    Updates CustomerLocation.CustomerLocationId @oldCustomerLocationId to @newCustomerLocationId
    
    Example:
        EXEC [dbo].[UpdateCustomerLocationId] 
            @oldCustomerLocationId = 6154874, 
            @newCustomerLocationId = 50334;
    */

BEGIN

    SET NOCOUNT ON;
    
    -- exit if @oldCustomerLocationId does not exists
    IF NOT EXISTS (SELECT * FROM dbo.CustomerLocation cl WHERE cl.CustomerLocationId = @oldCustomerLocationId)
    BEGIN
        PRINT CONCAT('CustomerLocationId ''', @oldCustomerLocationId, ''' (@oldCustomerLocationId) does not exist in dbo.CustomerLocation');
        RETURN 1; -- 0 = success, > 0 = failure
    END

    -- exit if @newCustomerLocationId already exists
    IF EXISTS (SELECT * FROM dbo.CustomerLocation cl WHERE cl.CustomerLocationId = @newCustomerLocationId)
    BEGIN
        PRINT CONCAT('CustomerLocationId ''', @newCustomerLocationId, ''' (@newCustomerLocationId) already exists in dbo.CustomerLocation');
        RETURN 2; -- 0 = success, > 0 = failure
    END
    

    BEGIN TRAN;


        BEGIN -- MOVE related data into temporary storage

            IF EXISTS (SELECT * FROM dbo.CustomerLocationData t WHERE t.CustomerLocationId = @oldCustomerLocationId) BEGIN
    
                IF OBJECT_ID('tempdb..#CustomerLocationData') IS NOT NULL
                    DROP TABLE #CustomerLocationData;

                SELECT * INTO #CustomerLocationData FROM dbo.CustomerLocationData t WHERE t.CustomerLocationId = @oldCustomerLocationId;
                DELETE t FROM dbo.CustomerLocationData t WHERE t.CustomerLocationId = @oldCustomerLocationId;

            END

        END


        BEGIN -- UPDATE dbo.CustomerLocation

            -- DROP CONSTRAINTs
            ALTER TABLE [dbo].[CustomerLocation] DROP CONSTRAINT [UC_CustomerLocation];

            -- INSERT OLD record with new CustomerLocationId
            SET IDENTITY_INSERT dbo.CustomerLocation ON;

            INSERT INTO dbo.CustomerLocation
            (
                CustomerLocationId, CustomerId, LocationId, CustomerLocationIdent, CustomerLocationIdent2, LocationIdent, LocationName, CustomerDistrictId, 
                CustomerLocationGUID, UpdatedOn, IssueManager, EnrollSelfMonitoring, TemperatureControlDeadlineHour, CreatedOn, OperationBegin, ActiveCustomer, 
                Comments, LocationName2, ParentGroup, TempString1, TempString2, TempString3, TempString4, TempString5, AutoInheritFromLocation, ClassificationPrimary
            )
            SELECT @newCustomerLocationId AS CustomerLocationId, CustomerId,LocationId, CustomerLocationIdent, CustomerLocationIdent2, LocationIdent, LocationName, CustomerDistrictId,
                CustomerLocationGUID, UpdatedOn, IssueManager, EnrollSelfMonitoring, TemperatureControlDeadlineHour, CreatedOn, OperationBegin, ActiveCustomer,
                Comments,LocationName2, ParentGroup, TempString1, TempString2, TempString3, TempString4, TempString5, AutoInheritFromLocation, ClassificationPrimary
            FROM dbo.CustomerLocation 
            WHERE CustomerLocationId = @oldCustomerLocationId;

            SET IDENTITY_INSERT dbo.CustomerLocation OFF;

            -- DELETE OLD record
            DELETE cl FROM dbo.CustomerLocation cl WHERE cl.CustomerLocationId = @oldCustomerLocationId;

            -- ADD CONSTRAINTS
            ALTER TABLE [dbo].[CustomerLocation] ADD CONSTRAINT [UC_CustomerLocation] UNIQUE NONCLUSTERED ([CustomerId], [LocationId]);

        END


        BEGIN -- re-INSERT related data from temporary storage

            IF OBJECT_ID('tempdb..#CustomerLocationData') IS NOT NULL BEGIN
                SET IDENTITY_INSERT dbo.CustomerLocationData ON;
                INSERT INTO dbo.CustomerLocationData (Guid, CustomerLocationId, CustomerLocationDataTypeId, Date, Category, Data)
                SELECT Guid, @newCustomerLocationId CustomerLocationId, CustomerLocationDataTypeId, Date, Category, Data FROM #CustomerLocationData;
                SET IDENTITY_INSERT dbo.CustomerLocationData OFF;
            END

        END


    COMMIT TRAN;

END

您不能更新标识列。 SQL Server不允许更新标识列，这与使用更新语句更新其他列不同。

尽管有一些替代方案可以实现类似的需求。

当需要为新记录更新标识列值时

使用DBCC CHECKIDENT检查表的当前标识值，如果需要，则更改标识值。

DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)

当需要更新现有记录的标识列值时

使用IDENTITY_INSERT，它允许显式的值插入到表的标识列中。

SET IDENTITY_INSERT YourTable {ON|OFF}

例子:

-- Set Identity insert on so that value can be inserted into this column
SET IDENTITY_INSERT YourTable ON
GO
-- Insert the record which you want to update with new value in the identity column
INSERT INTO YourTable(IdentityCol, otherCol) VALUES(13,'myValue')
GO
-- Delete the old row of which you have inserted a copy (above) (make sure about FK's)
DELETE FROM YourTable WHERE ID=3
GO
--Now set the idenetity_insert OFF to back to the previous track
SET IDENTITY_INSERT YourTable OFF