• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

setting identify field off

Experts-

I'm trying to update an ID field that is set as an identifying field.  I tried using the query below, but get an error that says,"cannot update identity column 'ID'  Can someone help me with this?

SET IDENTITY_INSERT [prospects] OFF
UPDATE prospects
set ID = 145191
where id = 145540
0
nmarano
Asked:
nmarano
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
Unfortunately, SQL does not allow UPDATE of the value of an IDENTITY column.
0
 
Scott PletcherSenior DBACommented:
You can do an INSERT and DELETE to get the same net effect (if you can serialize the table temporarily; otherwise someone else could insert into it and "steal" your identity number; if you know the table is not being INSERTed to, you can leave out the serialize / tablelock):

BEGIN TRANSACTION

SET IDENTITY_INSERT [prospects] ON

DBCC CHECKIDENT('dbo.prospects', RESEED, 145190)

INSERT INTO dbo.prospects (col1, col2) --, ...) --all cols EXCEPT ID
SELECT col1, col2 --, ...
FROM dbo.prospects WITH (TABLOCKX)
WHERE ID = 145540

COMMIT TRANSACTION

DELETE FROM dbo.prospects
WHERE ID = 145540

Open in new window

0
 
nmaranoAuthor Commented:
Thanks, I switched it to an insert,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now