Re: [sqlite] Help request for a query...
Igor Tandetnik wrote:
> Roger Andersson 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
Re: [sqlite] Help request for a query...
On 02/19/12 23:04, Igor Tandetnik wrote:
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:\'));
Thanks Igor!
--
Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help request for a query...
Roger Andersson 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help request for a query...
On Feb 19, 2012, at 6:16 PM, Roger Andersson wrote: > insert or replace One thing to keep in mind when using "insert or replace" is that this will create an entirely new record each and every single time. Which means the rowid is always going to change. Which makes it a very poor candidate as a surrogate key. Which is a bit of a bummer. In other words, "insert or replace" is not a good substitute for a merge statement, ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help request for a query...
On 02/19/12 16:59, Jörgen Hägglund wrote:
Hi all!
I'm not sure if it's my brain melting or what it might be... :-)
I have a table defined as:
CREATE TABLE IF NOT EXISTS History (Path TEXT UNIQUE NOT NULL, Hits
INTEGER DEFAULT 0)
Is there any way of making inserting data as follows;
- If Path does not exist in the table;
INSERT INTO History VALUES ('c:\', 1)
Should yield:
c:\,1
- But, if Path already exists do this:
UPDATE History SET Hits = Hits + 1 WHERE Path = 'c:\'
Should yield:
c:\,2
Of course, the 'c:\' is entered programmatically (using params).
Anyone up for modifying, explaining and solving this? :-)
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:\'));
sqlite> select * from History;
Path|Hits
c:\|1
sqlite> insert or replace into History (Path, Hits) VALUES ('c:\',
(select count(*)+ifnull(Hits,1) from History where path='c:\'));
sqlite> select * from History;
Path|Hits
c:\|2
sqlite> insert or replace into History (Path, Hits) VALUES ('c:\',
(select count(*)+ifnull(Hits,1) from History where path='c:\'));
sqlite> select * from History;
Path|Hits
c:\|3
--
mvh Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help request for a query...
On Feb 19, 2012, at 4:59 PM, Jörgen Hägglund wrote:
> INSERT INTO History VALUES ('c:\', 1)
> UPDATE History SET Hits = Hits + 1 WHERE Path = 'c:\'
Short of a merge statement, which SQLite lacks, you will indeed need to use two
statements.
For example, you could turn your first insert statement into a conditional
insert:
begin transaction;
insert or ignore into history ...
update history set hits = hits + 1 where ...
end transaction;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help request for a query...
Hi all!
I'm not sure if it's my brain melting or what it might be... :-)
I have a table defined as:
CREATE TABLE IF NOT EXISTS History (Path TEXT UNIQUE NOT NULL, Hits
INTEGER DEFAULT 0)
Is there any way of making inserting data as follows;
- If Path does not exist in the table;
INSERT INTO History VALUES ('c:\', 1)
Should yield:
c:\,1
- But, if Path already exists do this:
UPDATE History SET Hits = Hits + 1 WHERE Path = 'c:\'
Should yield:
c:\,2
Of course, the 'c:\' is entered programmatically (using params).
Anyone up for modifying, explaining and solving this? :-)
Best regards,
/Jörgen
*
* Engelska
* Svenska
* Franska
* Tyska
* Engelska
* Svenska
* Franska
* Tyska
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
