How to assign the results of a SQL query to another column?

Question

PostgreSQL

Good afternoon!
I have a test_field column and have a query that outputs the results as the


query itself:

SELECT my_table.id AS id, sum(child.qty) AS all_child_qty
FROM my_table
LEFT OUTER JOIN my_table as child
   ON child.my_path LIKE (my_table.my_path || '%')
GROUP BY my_table.id

Tell me, please, how can I write the values ​​of all_child_qty to the corresponding rows of the test_field column and do it in the same query as the calculation?
Everything is in one table my_table.
Thanks

Answer 1

Rewrite to correlated query:

update my_table set all_child_qty = (select sum(child.qty) from my_table child where child.my_path LIKE (my_table.my_path || '%'));

or update my_table set ... from (subquery) where ...

Answer 2

UPDATE my_table
LEFT OUTER JOIN my_table as child
   ON child.my_path LIKE (my_table.my_path || '%')
SET all_child_qty = sum(child.qty)