title: SQL UPDATE row Number
body: <p>I have a table serviceClusters with a column identity(1590 values). Then I have another table serviceClustersNew with the columns ID, text and comment. In this table, I have some values for text and comment, the ID is always 1. Here an example for the table: </p>
  
  <p>[1, dummy1, hello1; </p>
  
  <p>1, dummy2, hello2; </p>
  
  <p>1, dummy3, hello3; </p>
  
  <p>etc.]</p>
  
  <p>WhaI want now for the values in the column ID is the continuing index of the table serviceClusters plus the current Row number: In our case, this would be 1591, 1592 and 1593. </p>
  
  <p>I tried to solve the problem like this: First I updated the column ID with the maximum value, then I tryed to add the row number, but this doesnt work:</p>
  
  <pre><code>-- Update ID to the maximum value 1590
  UPDATE serviceClustersNew 
  SET ID = (SELECT MAX(ID) FROM serviceClusters);
  -- This command returns the correct values 1591, 1592 and 1593
  SELECT ID+ROW_NUMBER() OVER (ORDER BY Text_ID) AS RowNumber 
  FROM serviceClustersNew
  -- But I'm not able to update the table with this command
  UPDATE serviceClustersNew 
  SET ID = (SELECT ID+ROW_NUMBER() OVER (ORDER BY Text_ID) AS RowNumber FROM
  serviceClustersNew)
  </code></pre>
  
  <p>By sending the last command, I get the error "Syntax error: Ordered Analytical Functions are not allowed in subqueries.". Do you have any suggestions, how I could solve the problem? I know I could do it with a volatile table or by adding a column, but is there a way without creating a new table / altering the current table?</p>