Roger Andersson <[email protected]> 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:\'));
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
