title: sqlite CTE with UPDATE
body: <p>I hope this is not a duplicate, I red some posts, but could not figure out how to fix this.</p>
  
  <p>I have a table like this</p>
  
  <pre><code>CREATE TABLE yo (ad INTEGER PRIMARY KEY, pa INTEGER, pd INTEGER);
  
  INSERT INTO yo VALUES
  (1,1,1),(2,1,3),(3,1,4),(4,3,5),(5,4,2),(6,3,8),(7,1,9),(8,6,7),(9,3,6);
  .header on
  .mode column yo
  select * from yo;
  
  ad          pa          pd
  ----------  ----------  ----------
  1           1           1
  2           1           3
  3           1           4
  4           3           5
  5           4           2
  6           3           8
  7           1           9
  8           6           7
  9           3           6
  </code></pre>
  
  <p>I can create a temp table using CTE to obtain the depth level of col 'pd' like this</p>
  
  <pre><code>CREATE table ui AS 
   WITH RECURSIVE ui(a,l) AS
   ( VALUES(1,0)
      UNION ALL
      SELECT yo.ad, ui.l+1
        FROM yo JOIN ui ON yo.pa=ui.a
        WHERE yo.pa!=yo.ad
      ORDER BY 2 desc
    )
    SELECT a,l FROM ui;
  
  select * from ui;
  
  a           l
  ----------  ----------
  1           0
  2           1
  3           1
  4           2
  5           3
  6           2
  8           3
  9           2
  7           1
  </code></pre>
  
  <p>Then I want to ADD a col to table 'yo' and enter th ui.l in there</p>
  
  <pre><code>ALTER TABLE yo ADD COLUMN lv INTEGER;
  UPDATE yo SET lv=
    (SELECT ui.l
     FROM ui
     WHERE ui.a=yo.ad);
  
  select * from yo;
  ad          pa          pd          lv
  ----------  ----------  ----------  ----------
  1           1           1           0
  2           1           3           1
  3           1           4           1
  4           3           5           2
  5           4           2           3
  6           3           8           2
  7           1           9           1
  8           6           7           3
  9           3           6           2
  </code></pre>
  
  <p>All works fine. Now I like to combine the temp table 'ui' create and table 'yo' update in 1 request?</p>
  
  <p>I tried many many combanation bout could not find a solution, I sure this is obvious, but I am not fluent enough to get it.</p>
  
  <p>Should the CTE creation be  before the UPDATE like in </p>
  
  <p><a href="https://stackoverflow.com/questions/21819183/how-to-use-ctes-with-update-delete-on-sqlite">How to use CTE&#39;s with update/delete on SQLite?</a></p>
  
  <p>Or should the CTE be computed into a select inside the UPDATE</p>
  
  <p>Thanx in advance for any helps</p>
  
  <p>Cheers,
  Phi</p>