title: How to update an SQL table with a unique number within a range
body: <p>(This is a follow-up to <a href="https://stackoverflow.com/questions/8268337/how-to-constrain-the-number-of-records-allowed-in-an-sql-table/8268646#8268646">the answer by "onedaywhen" on a question</a> I posted earlier today.)</p>
  
  <p>Hi everyone. Say I have a table <code>MyTable</code> with two <code>int</code> fields, <code>PrimaryKey</code> and <code>MyNumber</code>. (And other fields not directly relevant to this question). <code>MyNumber</code> has a unique constraint and a check constraint limiting it to <code>BETWEEN 1 AND n</code>. (Let's say <code>n=5</code> for now.)  </p>
  
  <pre><code>1,2  
  2,NULL  
  3,5  
  4,NULL  
  5,NULL  
  6,1  
  7,NULL
  </code></pre>
  
  <p>How could an <code>UPDATE</code> be written to change the record where <code>PrimaryKey=2</code> so that <code>MyNumber</code> has a non-<code>NULL</code> value? I don't care what value it has, so long as it's not a <code>NULL</code> and meets it's two constraints of being unique and within range.</p>
  
  <p>I'm using MS SQL Server, but I'm hoping there's an answer using standard SQL.</p>
  
  <p>(I'm also hoping there won't have to be a table with the numbers 1 to n as contents.)</p>
  
  <p>Many thanks.</p>