You need to

set identity_insert YourTable ON

Then delete your row and reinsert it with different identity.

Once you have done the insert don't forget to turn identity_insert off

set identity_insert YourTable OFF

Through the UI in SQL Server 2005 manager, change the column remove the autonumber (identity) property of the column (select the table by right clicking on it and choose "Design").

Then run your query:

UPDATE table SET Id = Id + 1

Then go and add the autonumber property back to the column.

This can be done using a temporary table.

The idea

• disable constraints (in case your id is referenced by a foreign key)
• create a temp table with the new id
• delete the table content
• copy back data from the copied table to your original table
• enable previsously disabled constraints

SQL Queries

Let's say your test table have two additional columns (column2 and column3) and that there are 2 tables having foreign keys referencing test called foreign_table1 and foreign_table2 (because real life issues are never simple).

alter table test nocheck constraint all;
alter table foreign_table1 nocheck constraint all;
alter table foreign_table2 nocheck constraint all;
set identity_insert test on;

select id + 1 as id, column2, column3 into test_copy from test v;
delete from test;
insert into test(id, column2, column3)
select id, column2, column3 from test_copy

alter table test check constraint all;
alter table foreign_table1 check constraint all;
alter table foreign_table2 check constraint all;
set identity_insert test off;
drop table test_copy;

That's it.

Identity modifying may fail depending on a number of factors, mainly revolving around the objects/relationships linked to the id column. It seems like db design is as issue here as id's should rarely if ever change (i'm sure you have your reasons and are cascasding the changes). If you really need to change id's from time to time, I'd suggest either creating a new dummy id column that isn't the primary key/autonumber that you can manage yourself and generate from the current values. Alternately, Chrisotphers idea above would be my other suggestion if you're having issues with allowing identity insert.

Good luck

PS it's not failing because the sequential order it's running in is trying to update a value in the list to an item that already exists in the list of ids? clutching at straws, perhaps add the number of rows+1, then if that works subtract the number of rows :-S

I saw a good article which helped me out at the last moment .. I was trying to insert few rows in a table which had identity column but did it wrongly and have to delete back. Once I deleted the rows then my identity column got changed . I was trying to find an way to update the column which was inserted but - no luck. So, while searching on google found a link ..

1. Deleted the columns which was wrongly inserted
2. Use force insert using identity on/off (explained below)

http://beyondrelational.com/modules/2/blogs/28/posts/10337/sql-server-how-do-i-insert-an-explicit-value-into-an-identity-column-how-do-i-update-the-value-of-an.aspx