Click here to Skip to main content
14,451,614 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hello,

How to update identity column value in SQL SERVER?
I am using this query for update identity column value in sql server.

set IDENTITY_INSERT tableSalesQuotation ON
update tableSalesQuotation
set
SNO=SubQuery.SNO
from
(SELECT Row_Number() OVER (ORDER BY SNO) as SNO
FROM tableSalesQuotation
) SubQuery
set IDENTITY_INSERT tableSalesQuotation OFF


but it's getting error.
Cannot update identity column 'SNO'

I have to update existing Identity Column Value, it's a application need.

please help me.

Thanks in Advance.

Ankit Agarwal
Software Engineer
Posted
Updated 22-Sep-19 12:45pm
Rate this:
Please Sign up or sign in to vote.

Solution 1

It's a very, very poor idea.
The whole idea of an IDENTITY field is that the value is unique, not that it is sequential - which means that as rows are added and removed the values are not reused, but "holes" appear where values used to be. If you update them to give sequential values, then in 30 seconds there could be holes again!

It is also a dangerous thing to do; of you have other tables that refer to this value, you could well destroy your data integrity by doing this. And... What if you have multiple users? When data like this is updated, it can cause some horrible problems...

Please, don't do it. If you need sequential numbers, then use ROW_NUMBER to provide it, but don't update IDENTITY value to give sequential results - it is seriously not what it is there for.

If you still think you really, really, should do this, then a simple Google will find you how to do it... But seriously, if you have to, then there is a problem with your design!
   
Comments
[no name] 12-Apr-14 4:10am
   
It's my application need.
Jörgen Andersson 12-Apr-14 6:17am
   
Then your design problems go much further than your database.
[no name] 12-Apr-14 6:31am
   
I have to column update.
[no name] 12-Apr-14 6:32am
   
I don't have any option for my problem solved.
[no name] 12-Apr-14 6:32am
   
Please help me.
[no name] 12-Apr-14 6:33am
   
I don't have any option.
Rate this:
Please Sign up or sign in to vote.

Solution 2

If there is no dependency between the identity value of your table and other tables, you can easily change the new to be inserted value using this code:
DBCC CHECKIDENT ( [YourTabl], RESEED ,[new desired new value])

Also, if you need to eliminate holes in your identity values, you can
1 convert the identity field into an integer
2 edit the values of the identity field (now an integer), using forn instance excel incremental rows)
3 make sure there are no duplicate values
4 convert your column into an identity field again

Hope it helps
Ramon Baiges
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100