title: how to increment column value using update query
body: <p>I tried below query to update ID with unique values in table ELEM_DUP3 but all ID got updated with same value. </p>
  
  <pre><code>UPDATE DW.ELEM_DUP3
   FROM ( SELECT MAX(ID) AS ID
      FROM DW.ELEM ) S
   SET ID = (S.ID + 1)
  </code></pre>
  
  <pre class="lang-none prettyprint-override"><code>Data in table:      After Update:
  ID Name             ID Name
  5 test8              8 test8
  6 test9              8 test9
  7 test10             8 test10
  </code></pre>
  
  <p>Expected result:</p>
  
  <pre><code>ID Name
  8 test8
  9 test9
  10 test10
  </code></pre>
  
  <p>Could any one help where am I wrong?</p>