0

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

Pablo Venturino
  • 5,208
  • 5
  • 33
  • 41
  • 1
    This could get to be a huge drain on resource, essentially what you are doing is sorting, then updating every row with its order number. What happens when you have 3 million records and you delete row 1? 2,999,999 updates have to be issued! What are you trying to achieve here - maybe we can help you design something a bit better. – diagonalbatman Feb 01 '11 at 13:05
  • Well, you have a point here. But the table is not supposed to contain more than a couple hundred rows, so performance wouldn't be that much affected (besides, this query is part of a batch of maybe another hundred queries, so performance is already screwed). – Pablo Venturino Feb 01 '11 at 13:25
  • You won't get concurrency here. If session A deletes "two" at the same time as session B deletes "three" you'll hit locking issues. If it is a batch job, that might not be an issue. – Gary Myers Feb 01 '11 at 23:01

3 Answers3

4

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

update my_table set pos = rownum;
Benoit
  • 76,634
  • 23
  • 210
  • 236
  • Worked like a charm :) but that's assuming the rows were inserted in the sequence they belong. How can I specify the order in which I want the rows sequenced? For instance, if rows were inserted in order 4, 2, 1, 3 (and pos equaled rows ids), and I deleted row 2, then this query would assign sequences like 4=1, 1=2, 3=3, instead of 4=3, 1=1, 3=2. – Pablo Venturino Feb 01 '11 at 13:30
  • You can update simple views in Oracle. Maybe use `Update (select * from my_table order by id) set pos = rownum`. – Benoit Feb 01 '11 at 13:35
  • Damn. Looked promising, but didn't work. Says ORA-01732 - "data manipulation operation not legal on this view". :( – Pablo Venturino Feb 01 '11 at 13:43
  • 2
    Then: `update my_table outer set pos = (select r from (select rownum r, t.* from my_table) where id = outer.id )` but this might be very, very slow. – Benoit Feb 01 '11 at 13:48
  • Ok, now it worked :) I edited it a bit anyway to preserve ordering: `update my_table outer set pos = (select r from (select rownum r, t.id from my_table t order by t.pos) where id = outer.id )`. Thanks @Benoit! – Pablo Venturino Feb 01 '11 at 13:55
  • @Pablo: You're right, I totally forgot to insert the `ORDER BY` clause… – Benoit Feb 01 '11 at 13:58
0

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
Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
0

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.

Allan
  • 17,141
  • 4
  • 52
  • 69