title: SQL Update, incrementing value by 1
body: <p>My mind has gone blank...I am missing something obvious trying to write a small script:</p>
  
  <p>I have one table with various ID's:</p>
  
  <pre><code>TBL_USETHISID
  nextid int
  </code></pre>
  
  <p>I have another table with references:</p>
  
  <pre><code>TBL_REFS
  ref varchar(6)
  thisdate datetime
  nextid int
  </code></pre>
  
  <p>I want to take the ID from TBL_USETHISID and then update TBL_REFS so each row's ID is one more than the previous.  I will then select the max(nextid) from TBL_REFS and update TBL_USETHISID with the highest.</p>
  
  <p>I am struggling a bit with this, we have to use this instead of auto-incrememnt fields as these ID's are used across multiple tables.</p>
  
  <p>Obviously I have tried:</p>
  
  <pre><code>UPDATE TBL_REFS FROM TBL_USETHISID
  SET nextid = TBL_USETHISID.nextid + 1
  </code></pre>
  
  <p>Thanks for all your help in advance.</p>
  
  <p>EDIT - Sample data:</p>
  
  <p>TBLUSETHISID:</p>
  
  <pre><code>nextid
  7001
  </code></pre>
  
  <p>TBL_REFS</p>
  
  <pre><code>ref       thisdate               nextid
  0000123   2012-10-02 00:00:00        
  0000124   2012-10-02 00:00:00 
  0000125   2012-10-02 00:00:00 
  </code></pre>
  
  <p>After update:</p>
  
  <p>TBL_REFS</p>
  
  <pre><code>ref       thisdate               nextid
  0000123   2012-10-02 00:00:00    7001    
  0000124   2012-10-02 00:00:00    7002
  0000125   2012-10-02 00:00:00    7003
  </code></pre>
  
  <p>Then I would UPDATE TBL_USETHISID FROM TBL_REFS a set nextid = max(a.nextid)+1 to update the original table.  I hope my formatting is correct, i idented like code for readability.</p>