MySQL query to maintain sort-order in ascending order for self-referencing table

Question

I am using MySQL 5.6.17.

I have a self-referencing table let us say TableA with columns id (int), title (varchar(100)), parent_id(int), sort_order (int).

The parent_id column is a foreign key that refers to the id of the same table. In this way I maintain the N level of hierarchy.

The table data is as below :

id    title         parent   sort_order 
1     Item 1        NULL     1
2     Item 1.1      1        1 
3     Item 1.2      1        4
4     Item 1.3      1        5
5     Item 2        NULL     3
6     Item 2.1      5        1 
7     Item 2.1.1    6        4
8     Item 2.1.2    6        5
9     Item 2.2      5        3
10    Item 2.1.3    6        3

Here, the hierarchy is well maintained but NOT the sort-order. I want to re-order the items under each parent item.

The resultant data should be like below :

id    title         parent   sort_order 
1     Item 1        NULL     1
2     Item 1.1      1        1 
3     Item 1.2      1        2
4     Item 1.3      1        3
5     Item 2        NULL     2
6     Item 2.1      5        1 
7     Item 2.1.1    6        1
8     Item 2.1.2    6        2
9     Item 2.2      5        2
10    Item 2.1.3    6        3

I have tried the query shown below to re-order the sort order of each item under parent :

UPDATE TableA 
CROSS JOIN (SELECT @rownumber := 0) r
SET TableA.sort_order = (@rownumber := @rownumber + 1)
WHERE TableA.parent IN (SELECT t.id FROM TableA t);

But it returns error

Error Code: 1093
You can't specify target table 'TableA' for update in FROM clause

If I remove the where clause from the above query then it resets the sort order for each item in ascending order but I want each item under parent starts with sort order 1.

Any idea how to achieve it?

Thanks in advance.

Answer 1

Sample data:

CREATE TABLE t
    (`id` int, `title` varchar(10), `parent` varchar(4), `sort_order` int)
;

INSERT INTO t
    (`id`, `title`, `parent`, `sort_order`)
VALUES
    (1, 'Item 1', NULL, 1),
    (2, 'Item 1.1', '1', 1),
    (3, 'Item 1.2', '1', 4),
    (4, 'Item 1.3', '1', 5),
    (5, 'Item 2', NULL, 3),
    (6, 'Item 2.1', '5', 1),
    (7, 'Item 2.1.1', '6', 4),
    (8, 'Item 2.1.2', '6', 5),
    (9, 'Item 2.3', '5', 3),
    (10, 'Item 2.1.3', '6', 3)
;

Query:

update t
join (
  select 
  t.*,
  @so := if(coalesce(parent, '0') != @p, 1, @so + 1) as new_sort_order
  , @p := coalesce(parent, '0')
  from t,
  (select @so := 0, @p := null) var_init
  order by parent, id
  ) sq on t.id = sq.id
set t.sort_order = sq.new_sort_order;

Result:

select * from t;

| ID |      TITLE | PARENT | SORT_ORDER |
|----|------------|--------|------------|
|  1 |     Item 1 | (null) |          1 |
|  2 |   Item 1.1 |      1 |          1 |
|  3 |   Item 1.2 |      1 |          2 |
|  4 |   Item 1.3 |      1 |          3 |
|  5 |     Item 2 | (null) |          2 |
|  6 |   Item 2.1 |      5 |          1 |
|  7 | Item 2.1.1 |      6 |          1 |
|  8 | Item 2.1.2 |      6 |          2 |
|  9 |   Item 2.3 |      5 |          2 |
| 10 | Item 2.1.3 |      6 |          3 |

Keep in mind, that you have to specify a column in the order by that determines the sort_order. I was assuming it's id in this case.

• see it working live in an sqlfiddle