At 23:34 04/06/2016, you 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.
Definitely not as Ryan pointed out, and as the help file clearly states
otherwise:
"All common table expressions (ordinary and recursive) are created by
prepending a WITH clause in front of a
<http://www.sqlite.org/lang_select.html>SELECT,
<http://www.sqlite.org/lang_insert.html>INSERT,
<http://www.sqlite.org/lang_delete.html>DELETE, or
<http://www.sqlite.org/lang_update.html>UPDATE
statement."
I agree that in that simple example, using a CTE is just syntactic sugar:
CREATE TABLE A (
Id INTEGER NOT NULL PRIMARY KEY,
Item1 INT,
Item2 CHAR,
item3 CHAR);
with ints (n) as (select -5000 union all select n+1 from ints limit 10000)
INSERT INTO A (id) select n from ints;
CREATE TABLE B (
Id INTEGER NOT NULL PRIMARY KEY,
Item1 INT,
Item2 CHAR,
item3 CHAR);
INSERT INTO B (item1) VALUES
(83),(81),(76),(105),(116),(101),(32),(114),(111),(99),(107),(115),(33),(0);
-- make table B bigger (IDs in A and B only partl overlap)
with ints (n) as (select (select count(*) from B)+1 union all select
n+1 from ints limit 10000)
INSERT INTO B (id) select n from ints;
with C as (select id, item1, item2, item3 from B where item1 not null)
update A set
item1 = (select C.item1 from C where C.id = A.id),
item2 = (select char(C.item1) from C where C.id = A.id),
item3 = (select group_concat(item2, '') from A AA where AA.id
<= A.id)
where id in (select id from C);
select * from A where item2 not null;
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
