Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE my_table (
 item_id INT primary key, 
 item_name VARCHAR(30), 
 parent_id INT, 
 item_order DECIMAL
);
INSERT into my_table 
  (item_id, item_name, parent_id, item_order) values
  (523, 	'fish', 	1, 	1) 
, (562, 	'worm', 	1, 	2) 
, (612, 	'mice', 	1, 	3) 
, (251, 	'cheese', 	1, 	4) 
, (723, 	'ketchup', 	2, 	1) 
, (912, 	'pasta', 	2, 	2) 
, (52,  	'chips', 	2, 	3) 
7 rows affected

UPDATE
    my_table 
SET
    item_order = 1 - 0.1
    WHERE item_id = 612;
1 rows affected

SELECT *
FROM my_table
ORDER BY parent_id, item_order;


item_id
item_name
parent_id
item_order


612
mice
1
0.9

523
fish
1
1

562
worm
1
2

251
cheese
1
4

723
ketchup
2
1

912
pasta
2
2

52
chips
2
3


UPDATE 
    my_table
SET
    item_order = T2.row_number
FROM 
    (SELECT item_id, item_order, row_number()
        OVER (ORDER BY item_order)
        FROM my_table
        WHERE parent_id = (SELECT parent_id FROM my_table WHERE item_id = 612))
    AS T2
WHERE T2.item_id = my_table.item_id;
4 rows affected

SELECT *
FROM my_table
ORDER BY parent_id, item_order;


item_id
item_name
parent_id
item_order


612
mice
1
1

523
fish
1
2

562
worm
1
3

251
cheese
1
4

723
ketchup
2
1

912
pasta
2
2

52
chips
2
3

item_id	item_name	parent_id	item_order
612	mice	1	0.9
523	fish	1	1
562	worm	1	2
251	cheese	1	4
723	ketchup	2	1
912	pasta	2	2
52	chips	2	3