two updates using the same CTE
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?