CTEs cannot be used inside triggers for UPDATE statements. See near the bottom of: <http://sqlite.org/lang_createtrigger.html>
On December 12, 2017 6:44:35 PM EST, Shane Dev <[email protected]> wrote: >Hi, > >I have a hierarchical table - > >sqlite> .sch hierarchy >CREATE TABLE hierarchy(id integer primary key, parent references >hierarchy, >descrip text, status text); > >with some entries - > >sqlite> select * from hierarchy; >id parent descrip status >1 root open >2 1 branch1 open >3 1 branch2 open >4 3 branch22 open >5 4 branch222 open >6 1 branch3 open > >I can clear the status field of the second branch (i.e id = 3, 4 and 5) >with - > >with recursive cte(lev, id, parent, status) as (select 0, id, parent, >status from hierarchy where id=3 union all select lev+1, h.id, >h.parent, >h.status from hierarchy as h, cte where h.parent=cte.id) update >hierarchy >set status=null where id in (select id from cte); > >sqlite> select * from hierarchy; >id parent descrip status >1 root open >2 1 branch1 open >3 1 branch2 >4 3 branch22 >5 4 branch222 >6 1 branch3 open > >However, if I try to create a trigger with this statement - > >sqlite> create view vhierarchy as select * from hierarchy; >sqlite> create trigger thierarchy instead of delete on vhierarchy begin >with recursive cte(lev, id, parent, status) as (select 0, id, parent, >status from hierarchy where id=old.id union all select lev+1, h.id, >h.parent, h.status from hierarchy as h, cte where h.parent=cte.id) >update >hierarchy set status=null where id in (select id from cte); end; > >I get an error - > >Error: near "update": syntax error > >Can anyone see why? >_______________________________________________ >sqlite-users mailing list >[email protected] >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Sent from my Android device with K-9 Mail. Please excuse my brevity. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
