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 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"
You cannot update the CTE table, it doesn't exist anywhere and the
example certainly shouldn't suggest it like that either - did you mean
to update table t? Where did you find an example like that?
A CTE is a temporary VIEW to help you formulate the result of a select
in a specific way. Most of what can be accomplished with a CTE can also
be done with a sub-query, except it has some really nice other features,
but you can always think of it as a kind of sub-query. Just as it makes
no sense to try and UPDATE a sub-query, it makes no sense to update a
CTE, it's a view, not an actual data-wielding table.
This is an example of a CTE used in an UPDATE query that simply adds 100
to the IDs, probably not something you'd need to do, but it illustrates
the usage:
Imagine There is a Table "t" which has an integer ID column.
WITH CT(oldID, newID) AS (
SELECT ID, ID+100 FROM t
)
UPDATE t SET ID = (SELECT newID FROM CT WHERE oldID = ID);
Note that the only table that can be updated is t since it really
exists, CT doesn't really exist, it's only a view of t.
Hope that makes it more clear!
Ryan
PS: Here you can learn more accurately about CTEs in SQLite:
https://sqlite.org/lang_with.html
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
