1

Is it possible to do an update from a recursive query in one SQL statement without using a temporary table?

I am trying to set the very top boss id for each employee and I get the error

3707: Syntax error, expected something like a name or '(' between '(' and the 'with' keyword.

CREATE TABLE EMP(ID INTEGER, BOSS_ID INTEGER, TOP_BOSS_ID INTEGER);
INSERT INTO EMP VALUES(1,NULL,NULL);
INSERT INTO EMP VALUES(2,NULL,NULL);
INSERT INTO EMP VALUES(3,1,NULL);
INSERT INTO EMP VALUES(4,2,NULL);
INSERT INTO EMP VALUES(5,1,NULL);
INSERT INTO EMP VALUES(6,2,NULL);
INSERT INTO EMP VALUES(7,3,NULL);

UPDATE EMP
FROM
(
  WITH RECURSIVE EH(ID,BOSS_ID,SUPER_BOSS_ID,LEVEL) AS
  (
   SELECT ID,BOSS_ID,BOSS_ID,0
   FROM EMP
   UNION ALL
   SELECT E.ID,E.BOSS_ID,P.BOSS_ID,E.LEVEL+1
   FROM EH E JOIN EMP P ON E.SUPER_BOSS_ID = P.ID
  )
  SELECT * FROM EH
) X
SET TOP_BOSS_ID = X.BOSS_ID
WHERE X.SUPER_BOSS_ID IS NULL
AND X.BOSS_ID IS NOT NULL
;
flag

1 Answer

1

It looks like the SQL parser will not allow the WITH clause. If you set up a view to do the recursrion it works fine:

CREATE RECURSIVE VIEW EMPVIEW (ID,BOSS_ID,SUPER_BOSS_ID,LEVEL) 
  AS  (   SELECT ID,Coalesce(Boss_Id,0) AS BOSSID,Coalesce(BOSS_ID,0),0
          FROM EMP
             UNION ALL   
             SELECT E.ID,E.BOSS_ID,P.BOSS_ID,E.LEVEL+1   
             FROM EMPVIEW E JOIN EMP P 
             ON E.SUPER_BOSS_ID = P.ID  )  
   ;

UPDATE EMP
FROM
(SELECT * FROM EMPVIEW 
  WHERE SUPER_BOSS_ID IS NOT NULL
             QUALIFY ROW_NUMBER() OVER (PARTITION BY ID
                                             ORDER BY LEVEL DESC) = 1
 )   X
  SET TOP_BOSS_ID = X.SUPER_BOSS_ID
 WHERE X.ID = EMP.ID
 ;

(Check out the LEVEL is what you expect!) I had to use it slightly differently to make sure I was only returning a single row for every ID into the Update.

Sorry about the spacing - it gets corrupted when I paste it in!

link|flag

Your Answer

Get an OpenID
or

Not the answer you're looking for? Browse other questions tagged or ask your own question.