Update multiple records with different incremental values
<p>In this I have a table <code>Plant_Component</code> wich has a <code>CODE</code> column. 
We inserted in our web application some code to autocompute the value that will go in <code>Plant_Component.CODE</code> for new inserted records, and now I have to do some cleanup and insert the right value in my <code>Plant_Component</code> table with <code>CODE = 'testcode'</code>.</p>

<p>The logic for computing the code is:</p>

<ul>
<li>Get all the values from <code>Plant_Component.CODE</code></li>
<li>Select only the values wich are integer</li>
<li>From these integer values, get the maximum</li>
<li>Add 1 to the maximum value, and that's my code.</li>
</ul>

<p>The query I used is:</p>

<pre><code>UPDATE Plant_Component
SET CODE = CONVERT(varchar, CONVERT(integer, PCread.CODE) + 1)
FROM  
    (SELECT CODE 
    FROM Plant_Component
    WHERE ISNUMERIC(CODE) = 1
    AND CODE NOT LIKE  '%[^0-9]%'
) AS PCread
WHERE Plant_Component.CODE = 'testcode'
</code></pre>

<p>The problem is the same value gets inserted in every row affected by this query. To explain myself, when I tested this my maximum integer value was 507007, and every row affected was updated with the value 507008...</p>

<p>Is there a way to make it so every different row affected will increase this value by 1?</p>

<p>Example:
If at the moment I launch the query my max code is 507007, and the query affects three records, I expect these three records to have code respectively 507008, 507009, 507010.</p>

<p>NOTE: I know the way I identify a value as integer isn't foolproof, but this is a one-time-launch script, and I know there will be no values that will cause this to get an error (for example, i think value '9999999999999999999999999999999999' may crash this)</p>