PostgreSQL: implementing Recursive CTEs and Update Statements within the Same Query
I'm refactoring my project and I need help solving I'm stuck trying to I've been struggling with this for a few days now and could really use some help. I'm working with a question when trying to use a recursive Common Table Expression (CTE) to update data in a table. My goal is to traverse a hierarchy in my `employees` table and update each employee's `level` based on their depth in the hierarchy. However, I'm receiving the behavior `behavior: recursive CTE does not have a non-recursive term`. Here's the query I've been using: ```sql WITH RECURSIVE employee_hierarchy AS ( SELECT id, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.manager_id, eh.level + 1 FROM employees e JOIN employee_hierarchy eh ON e.manager_id = eh.id ) UPDATE employees SET level = eh.level FROM employee_hierarchy eh WHERE employees.id = eh.id; ``` I expected this to traverse the hierarchy and update the levels correctly, but it seems like the SQL Server is having trouble recognizing the `UPDATE` statement in conjunction with the recursive CTE. I've tried breaking it into two separate queries, where I first run the CTE and store the results in a temporary table, but I would prefer to keep it in one query for performance reasons. As an alternative, I also attempted to create a materialized view for the hierarchy and then perform the update, but that approach also didn't yield the desired outcome. Additionally, it appears that the CTE is functioning correctly when executed separately, as I can retrieve the levels without scenario: ```sql WITH RECURSIVE employee_hierarchy AS ( SELECT id, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.manager_id, eh.level + 1 FROM employees e JOIN employee_hierarchy eh ON e.manager_id = eh.id ) SELECT * FROM employee_hierarchy; ``` I am on PostgreSQL version 14.0. Any guidance on how to properly implement this recursive update would be greatly appreciated! Am I missing something obvious? Hoping someone can shed some light on this. I've been using Sql for about a year now. Has anyone dealt with something similar? I've been using Sql for about a year now. Any feedback is welcome!