Re: [sqlite] Help request for a query...

[Igor Tandetnik]

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...

[Roger Andersson]

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...

[Igor Tandetnik]

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...

[Petite Abeille]

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...

[Roger Andersson]

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...

[Petite Abeille]

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...

[Jörgen Hägglund]

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