sqlite CTE with UPDATE
This works:
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);This works too:
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 );