带更新的sqlite CTE [英] sqlite CTE with UPDATE

问题描述

我希望这不是重复的，我发了一些帖子，但不知道如何解决。

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并在其中输入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'create和表'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创建应该像

如何在更新/删除时使用CTE是SQLite吗？

还是应该将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
  );

这篇关于带更新的sqlite CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！