MySql/Oracle Remove gaps of numeric column

Question

First of all, I need a solution for Oracle and MySQL.

I Have a folder table :

id | name | parent_id | position
_________________________________
1  | root | null      | 1
2  | a    | 1         | 1
3  | b    | 1         | 2
4  | b1   | 3         | 1
5  | b2   | 3         | 2
6  | c    | 1         | 3
7  | d    | 1         | 4
8  | e    | 1         | 5

given the tree :

root
  |_ a
  |_ b
  |  |_b1
  |  |_b2
  |_c
  |_d
  |_e

The column position has a NOT NULL and UNIQUE constraint.

Problem :
Sometimes i have to delete some folders in a single query (ex : delete folder 'a', 'b1', 'd'). When doing this i have gaps in folders position :

id | name | parent_id | position
_________________________________
1  | root | null      | 1
3  | b    | 1         | 2
5  | b2   | 3         | 2
6  | c    | 1         | 3
8  | e    | 1         | 5

So I need to update the table in single request for updating the position column and in a specific order (to prevent the UNIQUE constraint) to obtain the result :

id | name | parent_id | position
_________________________________
1  | root | null      | 1
3  | b    | 1         | 2
5  | b2   | 3         | 1
6  | c    | 1         | 2
8  | e    | 1         | 3

Any Idea ?

Thanks

Answer 1

I solved the problem :

Oracle

    UPDATE folders t 
    SET position = ( select count(*)
    FROM folders f1 INNER JOIN folders f2 on ( f1.parent_id = f2.parent_id   and f1.position >= f2.position )
    WHERE f1.id = t.id AND t.parent_id = f1.parent_id
    GROUP BY f1.id, f1.position );

MySQL

    UPDATE folders f
    INNER JOIN ( select f1.id, f1.parent_id, count(*) as newPos
    FROM folders f1 INNER JOIN folders f2 on ( f1.parent_id = f2.parent_id and f1.position >= f2.position)
    GROUP BY f1.parent_id, f1.position) t on ( t.id = f.id and t.parent_id = f.parent_id)
    SET f.position = t.newPos