How to update identity column in SQL

There are times when we required identity columns values to updated as per the application logic and flow.

But how can update the identity column. Let us see below how we can achieve this.

We have to use IDENTITY_INSERT which allows explicit values to be inserted into the identity column of a table.

As this does not allow identity column to updated, It allows only insert. So we have to insert new entry and delete the old one.

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

Now I will demonstrate you with a real example. Currently we have a table Tbl_RegionTypeMaser where we need to update the Id to 99, because in our application it is required to have id =99 for zones.

This query we need to run to accomplish this task.

SET IDENTITY_INSERT Tbl_RegionTypeMaster ON;
GO
insert into Tbl_RegionTypeMaster (Id,Name,Level,IsActive,_deleted) values('99','Zone',99,1,0)
GO
delete from Tbl_RegionTypeMaster where Id=7
GO
SET IDENTITY_INSERT Tbl_RegionTypeMaster OFF;

Below are the updated records.

Hope you find it very easy.

Thanks and keep reading out blog and also you can alose view our Youtube channel https://www.youtube.com/channel/UC-76arHSXb07N19l-wuE_8A .