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
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
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 WITH CLAUSE make no sense
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);
INSER
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);
INSER
5 matches
Mail list logo