Postgres 10

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 int
)
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 t
SET item_order =
     case 
     when t.item_order = t2.item_order then 1
     when t.item_order < t2.item_order then t.item_order + 1
     else t.item_order
     end
FROM my_table t2
WHERE t.parent_id = t2.parent_id
  AND t.item_order <= t2.item_order
  AND t2.item_id = 612;
3 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	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