Recursive SQL with update query

Database

Recursive SQL with update query

I want to use the results obtained from a recursive query to update a table data. How can i do it.

for eg.

with RECURSIVE CTE (A,B,FLG) AS(

SELECT A,B,1 FROM TABLE1

UNION ALL

SELECT A,B,CASE WHEN A=3 THEN 1 ELSE 0 END FROM CTE WHERE FLG=1)

UPDATE TABLE1

FROM (SELECT A FROM CTE WHERE FLG=1 QUALIFY ROW_NUMBER() OVER( PARTITION BY A ORDER BY A)=1) TMP

SET A=TMP.A;

Can anyone help me in it. 

1 REPLY
Senior Apprentice

Re: Recursive SQL with update query

WITH can only be used with SELECT (just don't ask why):

Note: You can only use this statement modifier with SELECT statements. WITH cannot be used with other DML statements.

There's a workaround, create a Recursive View and use it instead.

Or materialize the Select in a Volatile Table.