Re: [sqlite] Easy question concerning C++ & sqlite3
your code worked perfectly, thx once more! vl.pavlov wrote: > > thank u very much, > > i'll try :) > > > > Dennis Cote wrote: >> >> vl.pavlov wrote: >>> hello & thanks 4 reply >>> >>> ok, i think i understand, >>> i would like that you, if not prob., write the whole solution once with >>> index on words >>> >> >> Here it is again. >> >> create table words (word text primary key, number integer); >> >> insert or replace into words values(:word, >> coalesce((select number from words where word = :word) + 1, 1)); >> >> I have changed the field name above from the original count to number to >> avoid confusion with the count() function in SQL. >> >> The primary key constraint on the word column creates a unique index on >> the words with the word field in ascending order. That index is used to >> quickly locate a word in the table, or determine that the word is >> missing from the table. >> >> The :word is a parameter to the insert statement that you bind to the >> word you want to insert. If you are generating the SQL as text then >> simply replace that identifier with a literal string containing the word >> to be inserted. >> >> HTH >> Dennis Cote >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > -- View this message in context: http://www.nabble.com/Easy-question-concerning-C%2B%2B---sqlite3-tp15561319p15731158.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Easy question concerning C++ & sqlite3
thank u very much, i'll try :) Dennis Cote wrote: > > vl.pavlov wrote: >> hello & thanks 4 reply >> >> ok, i think i understand, >> i would like that you, if not prob., write the whole solution once with >> index on words >> > > Here it is again. > > create table words (word text primary key, number integer); > > insert or replace into words values(:word, > coalesce((select number from words where word = :word) + 1, 1)); > > I have changed the field name above from the original count to number to > avoid confusion with the count() function in SQL. > > The primary key constraint on the word column creates a unique index on > the words with the word field in ascending order. That index is used to > quickly locate a word in the table, or determine that the word is > missing from the table. > > The :word is a parameter to the insert statement that you bind to the > word you want to insert. If you are generating the SQL as text then > simply replace that identifier with a literal string containing the word > to be inserted. > > HTH > Dennis Cote > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Easy-question-concerning-C%2B%2B---sqlite3-tp15561319p15633039.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Easy question concerning C++ & sqlite3
vl.pavlov wrote: > hello & thanks 4 reply > > ok, i think i understand, > i would like that you, if not prob., write the whole solution once with > index on words > Here it is again. create table words (word text primary key, number integer); insert or replace into words values(:word, coalesce((select number from words where word = :word) + 1, 1)); I have changed the field name above from the original count to number to avoid confusion with the count() function in SQL. The primary key constraint on the word column creates a unique index on the words with the word field in ascending order. That index is used to quickly locate a word in the table, or determine that the word is missing from the table. The :word is a parameter to the insert statement that you bind to the word you want to insert. If you are generating the SQL as text then simply replace that identifier with a literal string containing the word to be inserted. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Easy question concerning C++ & sqlite3
hello & thanks 4 reply ok, i think i understand, i would like that you, if not prob., write the whole solution once with index on words Igor Tandetnik wrote: > > "vl.pavlov" <[EMAIL PROTECTED]> wrote > in message news:[EMAIL PROTECTED] >> there is expression: >> >> select count(number) from words where word = bla >> >> which, if i am right, searches through whole table > > Not if you have an index on "word". And you need a unique index anyway > for "insert or replace" part to work (replace only kicks in if insert > would have violated a uniqueness constraint). > > Oh, and the subselect should use "select number ...", not "select > count(number) ...". > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Easy-question-concerning-C%2B%2B---sqlite3-tp15561319p15606736.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Easy question concerning C++ & sqlite3
"vl.pavlov" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > there is expression: > > select count(number) from words where word = bla > > which, if i am right, searches through whole table Not if you have an index on "word". And you need a unique index anyway for "insert or replace" part to work (replace only kicks in if insert would have violated a uniqueness constraint). Oh, and the subselect should use "select number ...", not "select count(number) ...". Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Easy question concerning C++ & sqlite3
hi again, i wander in this expression: * table words * * word | number * insert or replace into words values(bla', coalesce((select count(number) from words where word = bla)+1, 1)); there is expression: select count(number) from words where word = bla which, if i am right, searches through whole table -- i wander is there a way to use some other function? Dennis Cote wrote: > > vl.pavlov wrote: >> >> C++ aplication should put words into sqlite3 database, but if word that >> should be put into the dbase is already in the table of the dbase it >> should >> increase the number which is in the second column of the same row. how? >> >> eg: >> coffee | 3 >> milk | 1 >> >> ... >> >> i use standard functions from sqlite3.h >> > > You can try this: > > create table words (word text primary key, count integer); > > insert or replace into words values(:word, > coalesce((select count from words where word = :word) + 1, 1)); > > It will replace the row with a new one that has the count incremented if > the word already exists. It will insert the word with a count of 1 if not. > > HTH > Dennis Cote > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Easy-question-concerning-C%2B%2B---sqlite3-tp15561319p15585117.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Easy question concerning C++ & sqlite3
hello again & thx 4 reply i'll try with this. i am very new 2 sql and sqlite so i would like 2 find the fastest way to do the operation i explained in the first post thank you once more Dennis Cote wrote: > > vl.pavlov wrote: >> >> C++ aplication should put words into sqlite3 database, but if word that >> should be put into the dbase is already in the table of the dbase it >> should >> increase the number which is in the second column of the same row. how? >> >> eg: >> coffee | 3 >> milk | 1 >> >> ... >> >> i use standard functions from sqlite3.h >> > > You can try this: > > create table words (word text primary key, count integer); > > insert or replace into words values(:word, > coalesce((select count from words where word = :word) + 1, 1)); > > It will replace the row with a new one that has the count incremented if > the word already exists. It will insert the word with a count of 1 if not. > > HTH > Dennis Cote > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Easy-question-concerning-C%2B%2B---sqlite3-tp15561319p15584651.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Easy question concerning C++ & sqlite3
vl.pavlov wrote: > > C++ aplication should put words into sqlite3 database, but if word that > should be put into the dbase is already in the table of the dbase it should > increase the number which is in the second column of the same row. how? > > eg: > coffee | 3 > milk | 1 > > ... > > i use standard functions from sqlite3.h > You can try this: create table words (word text primary key, count integer); insert or replace into words values(:word, coalesce((select count from words where word = :word) + 1, 1)); It will replace the row with a new one that has the count incremented if the word already exists. It will insert the word with a count of 1 if not. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
