Link to home
Start Free TrialLog in
Avatar of nmarano
nmarano

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
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

Avatar of nmarano
nmarano

ASKER

Thanks, I switched it to an insert,