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

yes, i need to update the table but i search to do this in one request — Yoplaboom, Sep 28 '15 at 07:47
And the same SQL statement has to work with both MySQL and Oracle? Could be tricky... — jarlh, Sep 28 '15 at 07:56
@Yoplaboom please choose only one rdbms in yours tags, Oracle or MySql. My solution is for Oracle — Juan Carlos Oropeza, Sep 28 '15 at 07:57
@JuanCarlosOropeza Ok, i will remember for it but i used oracle and mysql tags because i need to do this with both of them :). — Yoplaboom, Sep 28 '15 at 08:00
Then you should explain that as part of your question. For your reference please read [How to Ask](http://stackoverflow.com/help/how-to-ask) — Juan Carlos Oropeza, Sep 28 '15 at 08:02
But why do you want to renumber? What kind of data has that need? — jarlh, Sep 28 '15 at 09:50
this column is position folders column, so when i delete multiple folders, i need to update all folders position. I will edit my topic to be more carefully in the afternoon — Yoplaboom, Sep 28 '15 at 10:26

score 1 · Answer 1 · answered Sep 28 '15 at 07:49

1

Try this

MERGE
INTO    YourTable t1
USING   (
        SELECT pk_id, gap_ID, row_num() over (order by gap_id) as newGap
        FROM YourTable t2
        ) as sub
ON      (t1.pk_id = t2.pk_id)
WHEN MATCHED THEN
UPDATE
SET     gap_ID = newGap;

answered Sep 28 '15 at 07:49

Juan Carlos Oropeza

47,252
12
78
118

You could also use `(order by pk_id)` – Juan Carlos Oropeza Sep 28 '15 at 07:49
Not sure if ON should be `(t1.pk_id = sub.pk_id)` but i guess you can play with the alias :) – Juan Carlos Oropeza Sep 28 '15 at 07:52

score 0 · Accepted Answer · answered Sep 30 '15 at 09:25

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

MySql/Oracle Remove gaps of numeric column

2 Answers2