sqlite CTE с обновлением

Question

Я надеюсь, что это не дубликат, я прочитал некоторые сообщения, но не мог понять, как это исправить.

У меня есть такой стол

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

Я могу создать временную таблицу, используя CTE, чтобы получить уровень глубины col 'pd', как это

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

Затем я хочу добавить столбец к таблице 'йо' и ввести там свой интерфейс.

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

Все работает отлично. Теперь мне нравится объединять временную таблицу 'ui' create и таблицу 'yo' update в 1 запросе?

Я перепробовал множество комбинаций, но не смог найти решение, я уверен, что это очевидно, но я не достаточно беглый, чтобы его найти.

Должно ли создание CTE быть до ОБНОВЛЕНИЯ, как в

Как использовать CTE с обновлением / удалением на SQLite?

Или CTE должен быть вычислен в выборку внутри ОБНОВЛЕНИЯ

Спасибо заранее за любую помощь

Ура, фи

Answer 1

Это работает:

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
 )
UPDATE yo SET lv=
 (SELECT ui.l
  FROM ui
  WHERE ui.a=yo.ad);

Это тоже работает:

UPDATE yo SET lv=
  (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 ui.l
   FROM ui
   WHERE ui.a=yo.ad
  );