Zack Weinberg <[email protected]> wrote:
> 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 event, I want to look up the row keyed by that
> tuple, and then either increment the occurrences cell if a row is
> found, or insert a new row (with occurrences equal to 1) if there was
> no match.
>
> 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
where src=:src and dest=:dest and verb=:verb),
0) + 1);
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
