On 2016/06/04 11:34 PM, Simon Slavin wrote:
On 4 Jun 2016, at 10:15pm, Jean-Christophe Deschamps <j...@antichoc.net> wrote:
Can't the same update be done more efficiently with a CTE?
The command inside the WITH has to be a SELECT command.
I wonder if there's a good reason for that. If the command inside WITH could
make changes to the database the result might be ambiguous, and very sensitive
to how the SQL engine works.
Well yes, WITH is a form of SELECT, so the function clause inside the
WITH has to be SELECT, in the same way that the function clause inside
an UPDATE statement has to be SET. However, the result of the WITH can
be applied to any Insert or Update or such (wherever a SELECT can be
used) - to demonstrate:
-- SQLite version 3.9.2 [ Release: 2015-11-02 ] on SQLitespeed
version 2.0.2.4.
-- Script Items: 5 Parameter Count: 0
-- 2016-06-05 02:26:52.452 | [Info] Script Initialized, Started
executing...
--
================================================================================================
CREATE TABLE Testy(
a INT,
b INT
);
WITH CTE(x) AS (
SELECT 1
UNION ALL
SELECT x+1 FROM CTE WHERE x<10
)
INSERT INTO Testy(a,b)
SELECT x,x*2 FROM CTE;
SELECT * FROM Testy;
-- a | b
-- ------------ | ------------
-- 1 | 2
-- 2 | 4
-- 3 | 6
-- 4 | 8
-- 5 | 10
-- 6 | 12
-- 7 | 14
-- 8 | 16
-- 9 | 18
-- 10 | 20
WITH CTE(x,y) AS (
SELECT 1,100
UNION ALL
SELECT x+1,y+(x*5) FROM CTE WHERE x<10
)
UPDATE Testy SET b=(SELECT CTE.y FROM CTE WHERE CTE.x = Testy.a
LIMIT 1) WHERE Testy.a < 8
SELECT * FROM Testy;
-- a | b
-- ------------ | -----
-- 1 | 100
-- 2 | 105
-- 3 | 115
-- 4 | 130
-- 5 | 150
-- 6 | 175
-- 7 | 205
-- 8 | 16
-- 9 | 18
-- 10 | 20
As to the OP's question of whether it can be done in a CTE (I assume he
means /using/ a CTE, and not necessarily having the working bits
/inside/ the CTE), there still is no way to use the UPDATE-FROM, even
from a CTE.
I might be wrong, but I think there is a problem with the UPDATE FROM
implementation; it has to require a scan one way or the other. Whether
you look up the value in the referenced table according to the key in
the updated table, or vice versa, there is no single-pass way of
avoiding the per-item lookup (unless somebody has done this - I'd be
very interested how). There is no way even for the QP to be sure a Key
column from both (or even one) of the tables will be involved in the
WHERE clause.
Perhaps if you have two tables that are exactly in sync (same Key-list,
same length), but that must be a serious edge-case.
The only advantage using the UPDATE-FROM syntax, which I can see, is
human legibility in SQL terms - which is not nothing btw.
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
