Igor Tandetnik <itandet...@mvps.org> wrote:
> Roger Andersson <r...@telia.com> wrote:
>> This seems to work but I do not fully understand why ;-)
>> sqlite> .header on
>> sqlite> CREATE TABLE IF NOT EXISTS History (Path TEXT UNIQUE NOT NULL,
>> Hits INTEGER);
>> sqlite> insert or replace into History (Path, Hits) VALUES ('c:\',
>> (select count(*)+ifnull(Hits,1) from History where path='c:\'));
>
> Since Path is unique, count(*) is either 0 or 1. If it's zero, then there's
> no corresponding record, so Hits is null, so
> ifnull(Hits, 1) is 1, so the result of subselect is 1. If count(*) is 1, then
> Hits has a value, and the result of subselect is
> Hits + 1.
>
> The same can be achieved in a less convoluted manner:
>
> insert or replace into History (Path, Hits) VALUES ('c:\',
> (select ifnull(Hits, 0) + 1 from History where path='c:\'));
On second thought, make it
insert or replace into History (Path, Hits) VALUES ('c:\',
ifnull((select Hits from History where path='c:\'), 0) + 1);
I don't think my first version works.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
