Calculate path to node in a tree

The following is part of a bigger system. The tree data table and temporary table cannot be changed easily so I need to work with those. The tree structure is more complex (deeper and wider) but the following are illustrative of the problem I am trying to solve.

I have a simple tree structure:

AAA     
  - aaa 
      - 111
      - 222

that is represented in data as:

Id  ParentId  Text
------------------ 
1   NULL      AAA       
2   1         aaa   
3   2         111
4   2         222

for a common function there is a temporary table where I want to determine which records need to be used to ultimately build the path to a particular item:

Id  ParentId  Text  UseRecord
----------------------------- 
1   NULL      AAA   False   
2   1         aaa   False       
3   2         111   False   
4   2         222   False

So for instance if I wanted to find which records are necessary to build the tree to item "222" after updating the temporary table it would look like:

Id  ParentId  Text  UseRecord
----------------------------- 
1   NULL      AAA   True    
2   1         aaa   True        
3   2         111   False   
4   2         222   True

What is the SQL statement I need to achieve the above result assuming it updates UseRecord and its only input is the Id value of 4 (ie "222"'s record).

This has to be really simple and fast, but for the life of me I cannot figure it out. All pointers appreciated.

You can use a recursive CTE to fetch Id at parent' levels:

DECLARE @Id int = 4;

WITH cte(level, Id, ParentId) AS(
    SELECT 0, Id, ParentId FROM @data WHERE id = @Id
    UNION ALL
    SELECT c.level+1, d.Id, d.ParentId FROM cte c
    INNER JOIN @data d ON c.ParentId = d.Id
)
SELECT Id, Level FROM cte
;

Output

With @Id = 4, This query returns the path with several Ids:

Id  Level
4   0
2   1
1   2

It only returns Ids on the path from the root up to the Id store in @Id. This is probably all is needed. I didn't try to update the @data table.

However this can easily be done by replacing the SELECT by an UPDATE:

WITH ...
...
UPDATE @data SET [UseRecord] = 1
WHERE Id IN (SELECT Id FROM cte)
;

SELECT * FROM @data;

Notes

It is uneasy to say whether it will be efficient and fast. There are not enough information and details in the question (data model, number of rows, quantity of data, indexes, etc.).

It is worth mentioning that it is probably a bad idea to update the temp table and it may also be a bad idea to use a temp table in the first place.

Perhaps the same result can be achieved with a single query (SELECT with cte) without creating a temp table. It could avoid tons of IO (read and write the temp table X times). The above query is a starting point anyhow.

Data used

DECLARE @data TABLE(Id int, ParentId int, [text] varchar(5));
INSERT INTO @data(Id, ParentId, [text]) VALUES
    (1, NULL, 'AAA')
    , (2, 1, 'aaa') 
    , (3, 2, '111')
    , (4, 2, '222');

Replace @data by your temp table name in the query.

StackExchange Database Administrators Q#138990, answer score: 3