How to update menu_order order in wp for all child posts when one of them changes order?

Question

SQL

There is a main record and child ones (An example of a hierarchy, chapters for books).
The user transfers, for example, chapter 3 out of 5 to the first position. It turns out that the old chapter 1 has position 1 and the 3rd chapter also has position 1.
The code below updates the order of all chapters based on the position of the chapters.
Is it possible somehow in Wordpress or still pure sql to update the positions of all child records with one query, while bypassing the problem of identical positions?

//Изменение позиции конкретной главы
$update_post = array(
  'ID' => $glav_id,
  'post_type' => 'book',
  'menu_order' => $position,
);
wp_update_post( wp_slash($update_post) );

//Обновление порядка всех глав (Сортировка при этом идет по столбцу menu_order и получается ошибочная логика, так как есть одинаковые позиции )
$wpdb->query( 'SELECT @i:=-1' );
$result = $wpdb->query("UPDATE {$wpdb->posts} SET menu_order = ( @i:= @i+1 ) WHERE post_parent = '{$post->post_parent}' AND post_author = '{$post->post_author}' ORDER BY menu_order ASC;");

Many thanks in advance for ideas and help. I did not find an answer to this question on the net and mostly these are plugins or on the admin side. I think the answer will be useful to everyone.

Answer 1

In pure SQL, the problem is solved with a simple query.
Example.
Source structure:

CREATE TABLE test (entity INT,    -- некая строка в структуре
                   position INT); -- её позиция при сортировке

Initial data to change:
@from - position number of the element to be moved somewhere;
@to - position number to which it should be moved.
Request:

UPDATE test
SET position := CASE WHEN position = @from 
                     THEN @to
                     ELSE position + SIGN(@from - @to)
                     END
WHERE position BETWEEN LEAST(@from, @to) AND GREATEST(@from, @to);

DEMO fiddle .