title: How to update an identity column in SQL Server by a constant shift?
body: <p>I have a table with something around 300 records in SQL Server 2017 Express. Now the issue is that some of the records got the ID shifted by a 1000. I need to update the records to discount the 1000 added.</p>
  
  <p>The problem is that (wrongly, I fully agree) this ID column is being used by the user of a system to identify orders. In that case, since this value is presented and used by the end user, they do not accept the skip. I fully agree it was a very wrong design when the system was created, but right now I need to correct the values.</p>
  
  <p>So I would like to do something like:</p>
  
  <pre><code>Update table SET Id = Id - 1000 WHERE Id &gt; 1000;
  </code></pre>
  
  <p>The issue is that the column is identity and SQL Server does not allow it to be updated.</p>
  
  <p>I can set the value on insert via <code>SET IDENTITY_INSERT table ON</code> but I cannot <em>change</em>.</p>
  
  <p>So how can I correct the IDs if the update is blocked? </p>
  
  <p>I've tried creating a column <code>IdTemp</code>, copying the values to it and update it. Then I considered dropping the <code>Id</code> column and making <code>IdTemp</code> identity, but I don't see how to turn a new column to identity.</p>
  
  <p>So how can I proceed?</p>