[sqlite] Application optimization puzzle: reading jobs from a table while writing results back to same table

I have a database with this (partial) schema: CREATE TABLE origins ( id INTEGER PRIMARY KEY, label TEXT NOT NULL UNIQUE ); CREATE TABLE url_strings ( id INTEGER PRIMARY KEY, urlTEXT NOT NULL UNIQUE ); CREATE TABLE canon_statuses ( id

Re: [sqlite] Increment counter or insert row in one statement

Igor Tandetnik wrote: > Zack Weinberg <za...@panix.com> wrote: [...] >> Is it possible to do this operation in one SQLite statement? > > insert or replace into observations > values (:src, :dest, :verb, > coalesce( >(select occurrences from observations >

Re: [sqlite] Increment counter or insert row in one statement

On Tue, Sep 7, 2010 at 4:45 AM, Simon Slavin <slav...@bigfraud.org> wrote: > On 7 Sep 2010, at 4:34am, Zack Weinberg wrote: > >> I'm not subscribed to the mailing list, please cc: me on replies. > > No.  If you don't read a mailing list, don't post to it. I have o

[sqlite] Increment counter or insert row in one statement

I have this database schema: CREATE TABLE observations ( src TEXT, dest TEXT, verb TEXT, occurrences INTEGER ); CREATE UNIQUE INDEX observations_index ON observations (src, dest, verb); My program receives a stream of events, which are (src, dest, verb) tuples. On every