mysql (innodb) increment a value to create a hole -

say have simple tree type table

id(key)     | parent | order ============================ 1           | 0      | 0 2           | 0      | 1 4           | 2      | 0 5           | 2      | 1 6           | 2      | 2 

i want insert new node has parent = 2 , order = 1, table data looks like:

id(key)     | parent | order ============================ 1           | 0      | 0 2           | 0      | 1 4           | 2      | 0 5           | 2      | 2 6           | 2      | 3 7           | 2      | 1 

e.g. existing rows increment order value.

what's best way ensure existing rows increment order (non-key field) starting @ existing arbitrary value, make hole insert statement?

if have new row parent $p , order position $o can:

update table set order = order + 1 parent = $p , order >= $o 

and then:

insert order (id,parent,order) values($id,$p,$o)