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
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 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 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
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
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
[sqlite] Easy question concerning C++ sqlite3
hello 2 all 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 thanx -- View this message in context: http://www.nabble.com/Easy-question-concerning-C%2B%2B---sqlite3-tp15561319p15561319.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
