title: Fill sequence in sql rows
body: <p>I have a table that stores a group of attributes and keeps them ordered in a sequence. The chance exists that one of the attributes (rows) could be deleted from the table, and the sequence of positions should be compacted.</p>
  
  <p>For instance, if I originally have these set of values:</p>
  
  <pre><code>+----+--------+-----+
  | id | name   | pos |
  +----+--------+-----+
  |  1 | one    |   1 |
  |  2 | two    |   2 |
  |  3 | three  |   3 |
  |  4 | four   |   4 |
  +----+--------+-----+
  </code></pre>
  
  <p>And the second row was deleted, the position of all subsequent rows should be updated to close the gaps. The result should be this:</p>
  
  <pre><code>+----+--------+-----+
  | id | name   | pos |
  +----+--------+-----+
  |  1 | one    |   1 |
  |  3 | three  |   2 |
  |  4 | four   |   3 |
  +----+--------+-----+
  </code></pre>
  
  <p>Is there a way to do this update in a single query? How could I do this?</p>
  
  <p>PS: I'd appreciate examples for both SQLServer and Oracle, since the system is supposed to support both engines. Thanks!</p>
  
  <p><strong>UPDATE:</strong> The reason for this is that users are allowed to modify the positions at will, as well as adding or deleting new rows. Positions are shown to the user, and for that reason, these should show a consistence sequence at all times (and this sequence must be stored, and not generated on demand).</p>