Geek Answers Handbook

Postgres recursive query to update field values ​​when moving parent_id

This is a table.

  user_id | parent_id | lft
  --------|-----------|-----
        1 |           | 0
        2 |         1 | 0
        3 |         1 | 0
        4 |         2 | 0

Here is a request to execute CTE from node 1 and move all child elements of user_id 1 until the sheet is reached, and update the value of the travesed chidren lft field to 1

WITH RECURSIVE d AS (
  SELECT user_id
   FROM btrees
   WHERE user_id = 1
 UNION ALL
  SELECT c.user_id
   FROM d JOIN btrees c ON c.parent_id = d.user_id
)
UPDATE btrees b set lft = 1
 FROM d
 WHERE d.user_id = b.user_id

I just ask for a request that will go in the opposite direction. from any node to the root of the node, so I can update the lft value

+3
source share
1 answer

A query that updates all nodes, starting from some node to the root, is very similar:

WITH RECURSIVE d AS (
  SELECT user_id
   FROM btrees
   WHERE user_id = :node_id
 UNION ALL
  SELECT c.user_id
   FROM d JOIN btrees c ON d.parent_id = c.user_id
)
UPDATE btrees b set lft = 1
 FROM d
 WHERE d.user_id = b.user_id

Please note that the condition in the connection is canceled.

In general, recursive queries work as follows:

  • UNION ALL WITH RECURSIVE.
  • UNION ALL , . . .
  • 2 , - .
+5

More articles:


All Articles