title: How to UPDATE table with changing values
body: <p>Hi I want to update a column to some values that can be changed during the update. e.g.</p>
  
  <pre><code>1: select * into #TempTable from (values ('a', 1), ('b', 1)) t (code, num)
  2: select * from #TempTable
  3: update tt set tt.code = 'b', tt.num = (
       select max(oo.num) + 1 from #TempTable oo where oo.code = 'b')
     from #TempTable tt
  4: select * from #TempTable
  5: drop table #TempTable
  </code></pre>
  
  <p>The output of statement 2: is </p>
  
  <pre><code>code num
  ---- ---
  a    1
  b    1
  </code></pre>
  
  <p>and statement 4: is</p>
  
  <pre><code>code num
  ---- ---
  b    2
  b    2
  </code></pre>
  
  <p>it seems as if the UPDATE statement is static, resulting both 'b' with num 2. What I really want to achieve is column num will change its value through out the whole UPDATE statement, i.e. for statement 4: I expect the output to be</p>
  
  <pre><code>code num
  ---- ---
  b    2
  b    3
  </code></pre>
  
  <p>How do you write the query to achieve that? I would like to avoid using cursors, hence answers without relying on cursors will be picked. If not possible without cursors, only then answers with cursors will be picked. Thank you.</p>