WITH "cte" is a table only WRT the UPDATE's RHS input space. eg:
CREATE TABLE t AS SELECT (column1)i,(NULL)a FROM (VALUES (1),(2),(3)); WITH cte(i,a) AS (VALUES (1,10),(2,20)) UPDATE t SET a=(SELECT a FROM cte WHERE i=t.i); SELECT * FROM t; i,a 1,10 2,20 3, [FYI. WITH ... INSERT/UPDATE/DELETE is not supported in a trigger body.] On Thu, Mar 8, 2018 at 8:09 PM, sanhua.zh <[email protected]> wrote: > I find that a UPDATE statement with WITH CLAUSE always fails, although I > use the syntax as SQLite syntax suggested. > Also, the `lang_with.html` do no show a UPDATE statement with WITH CLAUSE. > They all run in a SELECT statement. > > > Here is the sample SQL I tried: > ``` > CREATE TABLE t(i INTEGER); > INSERT INTO t VALUES(1); > WITH cte AS(SELECT i FROM t) > UPDATE cte SET i = 0; // Failed with "Error: no such table: cte" > ``` > > > Note that the sample WITH CLAUSE make no sense. It's just for testing. But > still failed. > Did I use it in a wrong way? What's the suggesting rule? > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
