title: SQL : update statement with dynamic column value assignment
body: <p>Imagine the following sql query:</p>
  
  <pre><code>UPDATE MYTABLE
  SET COL2 = (SELECT COL2 + 1 FROM (SELECT MAX(COL2) FROM MYTABLE) AS X)
  WHERE ID IN (1,2,3,4,5)
  </code></pre>
  
  <p>Assume that before the update is executed MAX(COL2) is 1.</p>
  
  <p>My intention is that for the update where ID=1 COL2 is updated to 'max(COL2) + 1'  (i.e. 2), and that for subsequent updates  'MAX(COL2) + 1' is re-evaluated, so that for ID=2, COL2=3 and ID=3, COL2=4  etc...</p>
  
  <p>What actually happens is that for all rows (ID=1,2,3,4,5), the value of COL2 is 2.</p>
  
  <p>Is there a smart way to have the  value of MAX(COL2) +1 "re-evaluated" at each update? I realize there may be performance issues with doing this, but I am curious none-the-less! Is there a better alternative (that does not involve multiple update statements) ?</p>
  
  <p>BTW: incase you are wondering about the syntax used for the above query (the nested inner table) see here: <a href="https://stackoverflow.com/questions/556237/sql-using-the-target-table-in-an-update-statement-in-a-nested-from-clause">SQL : Using the target table in an UPDATE statement in a nested FROM clause</a></p>