How to write the sql in SQLite with this?

    WITH cteCountDownlines AS
( --=== Count each occurrence of EmployeeID in the sort path
 SELECT EmployeeID = CAST(SUBSTRING(h.SortPath,t.N,4) AS INT), 
        NodeCount  = COUNT(*) --Includes current node
   FROM dbo.Hierarchy h, 
        dbo.HTally t
  WHERE t.N BETWEEN 1 AND DATALENGTH(SortPath)
  GROUP BY SUBSTRING(h.SortPath,t.N,4)
) --=== Update the NodeCount and calculate the RightBower
 UPDATE h
    SET h.NodeCount  = downline.NodeCount,
        h.RightBower = (downline.NodeCount - 1) * 2 + LeftBower + 1
   FROM dbo.Hierarchy h
   JOIN cteCountDownlines downline
     ON h.EmployeeID = downline.EmployeeID
;

that is from https://www.sqlservercentral.com/articles/hierarchies-on-steroids-1-convert-an-adjacency-list-to-nested-sets. I have tried many ways but not work. The separated cteCountDownlines and join works just fine, but don’t knwo how to conbine them

Answer

If you are using SQLite 3.33.0+ you can use the UPDATE...FROM syntax:

WITH cteCountDownlines AS (
 SELECT CAST(SUBSTR(h.SortPath,t.N,4) AS INT) EmployeeID, 
        COUNT(*) NodeCount
  FROM Hierarchy h INNER JOIN HTally t
  ON t.N BETWEEN 1 AND LENGTH(h.SortPath)
  GROUP BY SUBSTR(h.SortPath,t.N,4)
)
UPDATE Hierarchy AS h
SET NodeCount  = downline.NodeCount,
    RightBower = (downline.NodeCount - 1) * 2 + h.LeftBower + 1
FROM cteCountDownlines downline
WHERE h.EmployeeID = downline.EmployeeID;

For SQLite 3.15.0+ you can use this syntax:

WITH cteCountDownlines AS (
 SELECT CAST(SUBSTR(h.SortPath,t.N,4) AS INT) EmployeeID, 
        COUNT(*) NodeCount
  FROM Hierarchy h INNER JOIN HTally t
  ON t.N BETWEEN 1 AND LENGTH(h.SortPath)
  GROUP BY SUBSTR(h.SortPath,t.N,4)
)
UPDATE Hierarchy AS h
SET (NodeCount, RightBower) = (
  SELECT downline.NodeCount,
         (downline.NodeCount - 1) * 2 + h.LeftBower + 1
  FROM cteCountDownlines downline
  WHERE h.EmployeeID = downline.EmployeeID
);

Note that there is no equivalent to SQL Server’s DATALENGTH() function.
The function LENGTH() I used returns the number of chars in a string.