I want to use the results obtained from a recursive query to update a table data. How can i do it.
with RECURSIVE CTE (A,B,FLG) AS(
SELECT A,B,1 FROM TABLE1
SELECT A,B,CASE WHEN A=3 THEN 1 ELSE 0 END FROM CTE WHERE FLG=1)
FROM (SELECT A FROM CTE WHERE FLG=1 QUALIFY ROW_NUMBER() OVER( PARTITION BY A ORDER BY A)=1) TMP
Can anyone help me in it.
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.