带更新的 sqlite CTE

2022-01-17 00:00:00 sql-update common-table-expression sqlite

我希望这不是重复的，我红了一些帖子，但不知道如何解决这个问题.

I hope this is not a duplicate, I red some posts, but could not figure out how to fix this.

我有一张这样的桌子

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' 的深度级别

I can create a temp table using CTE to obtain the depth level of col 'pd' like this

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

然后我想在表 'yo' 中添加一个 col 并在其中输入 th ui.l

Then I want to ADD a col to table 'yo' and enter th ui.l in there

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

一切正常.现在我想在 1 个请求中结合临时表ui"创建和表yo"更新?

All works fine. Now I like to combine the temp table 'ui' create and table 'yo' update in 1 request?

我尝试了很多组合回合都找不到解决方案，我确定这很明显，但我不够流利，无法得到它.

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.

CTE 创建是否应该像

Should the CTE creation be before the UPDATE like in

如何在 SQLite 上使用 CTE 和更新/删除?

或者应该将 CTE 计算到 UPDATE 内的选择中

Or should the CTE be computed into a select inside the UPDATE

感谢任何帮助

干杯，飞

推荐答案

这可行:

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
  );

相关文章