How to use WITH CLAUSE in a UPDATE statement?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

How to use WITH CLAUSE in a UPDATE statement?

sanhua.zh
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

petern
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
In reply to this post by sanhua.zh
On 9 Mar 2018, at 4:09am, sanhua.zh <[hidden email]> 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. It's just for testing. But still failed.
> Did I use it in a wrong way? What's the suggesting rule?

The table called "cte" is not saved in the database.  Think of it as like a temporary index used for an ORDER BY, and deleted immediately the statement ends.  Therefore you cannot INSERT or DELETE into it either.  You are expected to SELECT from it, not to modify it to change how the WITH command works.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

R Smith-2
In reply to this post by sanhua.zh

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users