Setting muliple rows in postgres based on the set values of previous postgres rows

Question

I'm running postgres 9.4

I'm essentially updating an existing unorganized structure to a folder based organization. Im auto-assigning an order number to each item for user reordering, but doing an initial setting of all of these values with a 1 time use update statement. However, It seems like SET is taking my subquery's from clause and not recreating it for each successive row that it sets.

Here's my query example:

UPDATE folder_items
SET order_number = 
(SELECT COALESCE(MAX(folder_items_2.order_number), 0) + 1
FROM folder_items AS folder_items_2
WHERE folder_items.parent_folder_id = folder_items_2.parent_folder_id
AND folder_items.folder_set_id = folder_items_2.folder_set_id
AND folder_items.id != folder_items_2.id);

With my initial table:

         | folder_id    | folder_set_id | order_number
row 1    | 1            | 1             | null
row 2    | 2            | 1             | null
row 3    | 3            | 2             | null
row 4    | 4            | 2             | null
row 5    | 5            | 2             | null
row 6    | 6            | 3             | null

when I run my query I get something like

         | folder_id    | folder_set_id | order_number
row 1    | 1            | 1             | 1
row 2    | 2            | 1             | 1
row 3    | 3            | 2             | 1
row 4    | 4            | 2             | 1
row 5    | 5            | 2             | 1
row 6    | 6            | 3             | 1

However, I want results that look like this:

         | folder_id    | folder_set_id | order_number
row 1    | 1            | 1             | 1
row 2    | 2            | 1             | 2
row 3    | 3            | 2             | 1
row 4    | 4            | 2             | 2
row 5    | 5            | 2             | 3
row 6    | 6            | 3             | 1

Is there a way to get these desired results? Is the best way to do some sort of window function that counts how many in the same folder_set_id are underneath each row?

The problem is you think the update will be reflected instanly, but the true is only will be update when the query end, so every time you call it will see the original version. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Jan 17, 2017 at 22:01
Or do I have to do something like order them all numerically, and then subtract by the number of non-folder-set items in the window underneath? — John Kulp
– John Kulp, Commented Jan 17, 2017 at 22:05

Juan Carlos Oropeza · Accepted Answer · 2017-01-17 22:07:29Z

1

Use ROW_NUMBER to calculate the ORDER_ID, then update the table.

with new_order as (
      SELECT "folder_id", 
              row_number() over ( partition by "folder_set_id"
                                  order by "folder_id") as rn
      FROM Table1
)
UPDATE Table1 AS t
SET "order_number" = n.rn
FROM new_order AS n
WHERE t."folder_id" = n."folder_id";

SQL DEMO

OUTPUT

| row_id | folder_id | folder_set_id | order_number |
|--------|-----------|---------------|--------------|
|  row 1 |         1 |             1 |            1 |
|  row 2 |         2 |             1 |            2 |
|  row 3 |         3 |             2 |            1 |
|  row 4 |         4 |             2 |            2 |
|  row 5 |         5 |             2 |            3 |
|  row 6 |         6 |             3 |            1 |

answered Jan 17, 2017 at 22:07

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Setting muliple rows in postgres based on the set values of previous postgres rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related