Update all affected rows with specific values on PostgreSQL

You can do this with an update that gets the item_order from the item_id you want to move.

Only those orders below that changed order also need to move.

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

Demo on db<>fiddle here

Find the parent_id record with item_id = 1, and then update all records whose parent_id equals parent_id.

UPDATE my_table 
SET
   item_order = 1
WHERE parent_id = (SELECT parent_id FROM my_table WHERE item_id = 1);

You want to move an item in the sorted list. For this to happen you must change the item's sort key to the new position and increase all existing sort keys from that position on by one.

set @item_id = 612;
set @item_order = 1;

UPDATE my_table 
SET item_order =
   case when item_id = @item_id then @item_order
        when item_order >= @item_order then item_order + 1
        else item_order
   end
WHERE parent_id = (SELECT parent_id FROM my_table WHERE item_id = @item_id);

I ended up solving this by using decimals for item_order column.

If you try to set the item_order of 612 to 1, decrement its new item_order value by a dummy float.

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

Now the table looks like this:

Then go over the rows with the same parent_id and set their item_order to the value of row_number() function.

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;

Here is the result:

This example above only inserts before the given value. The dummy float would have to be added to item_order for inserting after a given value. Another issue is that all rows within the same parent id are updated regardless of whether they need an update or not.

db<>fiddle