two updates using the same CTE

Question

I have to "unremove" folders in an arborescence (thay have been flagged as removed, I change the value of the flag). Each folder can contain files or folders (which are stored in different tables). I have a CTE that defines all the folders that need to be updated.

 WITH arbre(id) AS(
     SELECT idDossier
     FROM portail_managers_dossier
     WHERE idDossier = @id
     UNION ALL
     SELECT d.idDossier
     FROM portail_managers_dossier AS d
     INNER JOIN arbre AS a
     ON a.id = d.idParent)

Then I have two UPDATE request, one for each table

 UPDATE portail_managers_dossier
 SET dtDateSuppr = NULL
 WHERE idDossier IN (SELECT id FROM arbre);


 UPDATE portail_managers_document
 SET dtDateSuppr = NULL
 WHERE idDossier IN (SELECT id FROM arbre);

My problem is : I don't know how to merge two UPDATE requests on different tables. The CTE only exists until the end of the request, so I have to define it twice. Is there any way to write all of the above code in a single request ?

Answer 1

As you have discovered, CTE's will lose scope after the first update. But, instead of using a CTE, why not write the results of the query within the CTE to a temp table, and do your updates based on the contents temp table?