Re: [sqlite] WAL: no schema after close

[Roger Binns]

On 03/06/16 18:56, Gelin Yan wrote:
>APSW is great, I have used it for years. I want to know whether it
> support pypy. I have switched to pypy for a while.

APSW at its heart is very much a CPython extension and uses that C API
to bind to SQLite.  I did port APSW to pypy a few years ago which
required disabling some things (eg the shell) due to missing APIs in
pypy.  However IIRC they didn't provide enough of the C API and while I
was willing and trying to push things forwards, no one involved with
pypy project was interested.  Try following the build instructions with
whatever version of pypy you are using and see what happens.

Roger




signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE statement without FROM clause

[Keith Medcalf]

You can simulate either a two-pass or one-pass UPDATE  SET ... FROM 
,  WHERE 

By doing one or the other of the following (depending on whether you want 
one-pass or two-pass).

for a one-pass update:

BEGIN IMMEDIATE;
SELECT .rowid,  FROM  WHERE 
fetch a row
  UPDATE  SET x=?, ...  WHERE rowid=? -- bind the result set to the 
parameters and execute
when you run out of rows,
COMMIT;

For a two pass update BEGIN IMMEDIATE then either (a) SELECT into a temp table 
and do the update above from that table, or (b) store the rows in memory and 
then do the update afterwards.  If you used a temp table, drop it before 
committing.

> I am using quite often SQL statements that update the data of one table
> with data from another table. This leads to some quite complex (and slow)
> statements because SQLite3 is not supporting a FROM clause in update
> statements. I am just wondering why the FROM clause is not supported by
> SQLite3?! Is this too complex to implement or is there simply no demand
> for these type of statements?




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE statement without FROM clause

[Jean-Christophe Deschamps]

At 23:34 04/06/2016, you wrote:

On 4 Jun 2016, at 10:15pm, Jean-Christophe Deschamps 
 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 
SELECT, 
INSERT, 
DELETE, or 
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 1)
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 1)

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