CODEWITHSUNDEEP
sqlpostgresql
Yılmaz Kasap
Yılmaz Kasap

Reputation: 160

Update order of all affected rows on PostgreSQL

I have the simplified version of the table I have below. Each row has an item_order value partitioned by its parent_id.

item_id item_name parent_id item_order
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

Let's say that I want to set the 'item_order' value of 'mice' to 1.

UPDATE
    my_table 
SET
    item_order = 1
    WHERE item_id = 612;

I need the table to be updated as below. (Expected changes are highlighted.)

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

How do I update the item_order value of the all affected rows within the same parent_id?

Upvotes: 3

Views: 1268

Answers (4)

Yılmaz Kasap
Yılmaz Kasap

Reputation: 160

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:

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

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:

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

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

Upvotes: 0

LukStorms
LukStorms

Reputation: 29647

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
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

Demo on db<>fiddle here

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

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);

Upvotes: 1

persian-theme
persian-theme

Reputation: 6638

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);

Upvotes: 1

Related Questions