Re: [sqlite] How to use WITH CLAUSE in a UPDATE statement?

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 ...

Re: [sqlite] How to use WITH CLAUSE in a UPDATE statement?

On 2018/03/09 8:43 AM, sanhua.zh 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

Re: [sqlite] How to use WITH CLAUSE in a UPDATE statement?

On 9 Mar 2018, at 4:09am, sanhua.zh wrote: > 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

[sqlite] How to use WITH CLAUSE in a UPDATE statement?

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);

[sqlite] How to use WITH CLAUSE in a UPDATE statement?

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);