title: How to update with incrementing value
body: <p>I have a table in PostgreSQL that has an ID column that is supposed to be unique.  However, a large number of the rows (around 3 million) currently have an ID of "1".</p>
  
  <p>What I know:</p>
  
  <ul>
  <li>The total number of rows</li>
  <li>The current maximum value for the ID column</li>
  <li>The number of rows with an (incorrect) ID of "1"</li>
  </ul>
  
  <p>What I need is a query that will pull all the rows with an ID of "1" and assign them a new ID that increments automatically so that every row in the table will have a unique ID.  I'd like it to start at the <code>currentMaxId + 1</code> and assign each row the subsequent ID.</p>
  
  <p>This is the closest I've gotten with a query:</p>
  
  <pre><code>UPDATE table_name
  SET id = (
      SELECT max(id) FROM table_name
  ) + 1
  WHERE id = '1'
  </code></pre>
  
  <p>The problem with this is that the inner SELECT only runs the first time, thus setting the ID of the rows in question to the original <code>max(id) + 1</code>, not the new <code>max(id) + 1</code> every time, giving me the same problem I'm trying to solve.<br>
  Any suggestions on how to tweak this query to achieve my desired result or an alternative method would be greatly appreciated!</p>