Fill sequence in sql rows

Question

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.

For instance, if I originally have these set of values:

+----+--------+-----+
| id | name   | pos |
+----+--------+-----+
|  1 | one    |   1 |
|  2 | two    |   2 |
|  3 | three  |   3 |
|  4 | four   |   4 |
+----+--------+-----+

And the second row was deleted, the position of all subsequent rows should be updated to close the gaps. The result should be this:

+----+--------+-----+
| id | name   | pos |
+----+--------+-----+
|  1 | one    |   1 |
|  3 | three  |   2 |
|  4 | four   |   3 |
+----+--------+-----+

Is there a way to do this update in a single query? How could I do this?

PS: I'd appreciate examples for both SQLServer and Oracle, since the system is supposed to support both engines. Thanks!

UPDATE: 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).

Answer 1

Not sure it works, But with Oracle I would try the following:

update my_table set pos = rownum;

Answer 2

this would work but may be suboptimal for large datasets:

SQL> UPDATE my_table t
  2     SET pos = (SELECT COUNT(*) FROM my_table WHERE id <= t.id);

3 rows updated

SQL> select * from my_table;

        ID NAME              POS
---------- ---------- ----------
         1 one                 1
         3 three               2
         4 four                3

Answer 3

Do you really need the sequence values to be contiguous, or do you just need to be able to display the contiguous values? The easiest way to do this is to let the actual sequence become sparse and calculate the rank based on the order:

select id, 
       name, 
       dense_rank() over (order by pos) as pos,
       pos as sparse_pos 
from my_table

(note: this is an Oracle-specific query)

If you make the position sparse in the first place, this would even make re-ordering easier, since you could make each new position halfway between the two existing ones. For instance, if you had a table like this:

+----+--------+-----+
| id | name   | pos |
+----+--------+-----+
|  1 | one    | 100 |
|  2 | two    | 200 |
|  3 | three  | 300 |
|  4 | four   | 400 |
+----+--------+-----+

When it becomes time to move ID 4 into position 2, you'd just change the position to 150.

---

Further explanation:

Using the above example, the user initially sees the following (because you're masking the position):

+----+--------+-----+
| id | name   | pos |
+----+--------+-----+
|  1 | one    |   1 |
|  2 | two    |   2 |
|  3 | three  |   3 |
|  4 | four   |   4 |
+----+--------+-----+

When the user, through your interface, indicates that the record in position 4 needs to be moved to position 2, you update the position of ID 4 to 150, then re-run your query. The user sees this:

+----+--------+-----+
| id | name   | pos |
+----+--------+-----+
|  1 | one    |   1 |
|  4 | four   |   2 |
|  2 | two    |   3 |
|  3 | three  |   4 |
+----+--------+-----+

The only reason this wouldn't work is if the user is editing the data directly in the database. Though, even in that case, I'd be inclined to use this kind of solution, via views and instead-of triggers.