Re: [sqlite] newB question; c++ and sqlite3; how2 check if word exist in the table
it works fine!
*
This is what I thought you wanted. Using your table and column names my
previous suggestion becomes:
insert into tbl1
select 'seaside'
where not exists
(select words from tbl2 where words = 'seaside')
The subselect will determine if the word is in tbl2 or not. If the word
exists in tbl2, the subselect will return a row, so the where condition
(not exists) on the select will be false, and the word will not be
inserted into table tbl1. If the word doesn't exist in tbl2, the where
condition will be true, and the word will be inserted into table tbl1.
**
i wander is there a way to create combination of these 2 query:
tbl1:word | number
tbl2:word
insert into tbl1
select 'seaside'
where not exists
(select words from tbl2 where words = 'seaside')
and
insert or replace into tbl1 values('seaside',coalesce
((select number from tbl1 where word = 'seaside') + 1, 1));
if there is 'seaside' in tbl2 nothing should be done, but if there is not
seaside should be put into tbl1 and counter should increase
i am sorry if i am annoying, i suppose that this question should solve this
prob.. also i shall learn about databases a lot in the future so maybe i
post more questions in future :)
--
View this message in context:
http://www.nabble.com/newB-question--c%2B%2B-and-sqlite3--how2-check-if-word-exist-in-the-table-tp15758100p16118115.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] newB question; c++ and sqlite3; how2 check if word exist in the table
here is example:
sqlite> create table tbl1(words text);
sqlite> create table tbl2(words text);
sqlite> insert into tbl2 (words) values ('seaside');
now i wish to insert into tbl1 some word, but if that word exist (eg
seaside) in tbl2 to ignore (word should not be inserted)
Dennis Cote wrote:
>
> vl.pavlov wrote:
>>
>> i wander how to do this query:
>>
>> insert into tablename values ('word')
>> but if does not exist the same word in other table
>>
>
> I'm not sure if this is what you are asking, but you can try this:
>
> insert into tablename
> select :word
> where not exists (select word from other_table where word = :word)
>
> 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/newB-question--c%2B%2B-and-sqlite3--how2-check-if-word-exist-in-the-table-tp15758100p16048124.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] newB question; c++ and sqlite3; how2 check if word exist in the table
here is example:
sqlite> create table tbl1(words text);
sqlite> create table tbl2(words text);
sqlite> insert into tbl2 (words) values ('seaside');
now i wish to insert into tbl1 some word, but if that word exist in tbl2 to
ignore (word should not be inserted)
Dennis Cote wrote:
>
> vl.pavlov wrote:
>>
>> i wander how to do this query:
>>
>> insert into tablename values ('word')
>> but if does not exist the same word in other table
>>
>
> I'm not sure if this is what you are asking, but you can try this:
>
> insert into tablename
> select :word
> where not exists (select word from other_table where word = :word)
>
> 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/newB-question--c%2B%2B-and-sqlite3--how2-check-if-word-exist-in-the-table-tp15758100p16048123.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] newB question; c++ and sqlite3; how2 check if word exist in the table
hello once more
i wander how to do this query:
insert into tablename values ('word')
but if does not exist the same word in other table
i suppose that with combination of your answers to this and my other post
solution can be created, but i do not know how exactly :)
thanx
vl.pavlov wrote:
>
> dear Dennis
>
> thank u 4 reply once more, i'll try with your suggestion
>
> vl
>
>
> Dennis Cote wrote:
>>
>> vl.pavlov wrote:
>>>
>>> i wander how 2 check (efficiently) if some word (string, in c++) exist
>>> in
>>> the sqlite3 table
>>>
>>
>> Assuming this is the same database as your last question. You have the
>> table:
>>
>> create table words (word text primary key, number integer);
>>
>> Then this query will work:
>>
>> select exists (select * from words where word = :word);
>>
>> 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/newB-question--c%2B%2B-and-sqlite3--how2-check-if-word-exist-in-the-table-tp15758100p15950680.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] newB question; c++ and sqlite3; how2 check if word exist in the table
dear Dennis thank u 4 reply once more, i'll try with your suggestion vl Dennis Cote wrote: > > vl.pavlov wrote: >> >> i wander how 2 check (efficiently) if some word (string, in c++) exist in >> the sqlite3 table >> > > Assuming this is the same database as your last question. You have the > table: > > create table words (word text primary key, number integer); > > Then this query will work: > > select exists (select * from words where word = :word); > > 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/newB-question--c%2B%2B-and-sqlite3--how2-check-if-word-exist-in-the-table-tp15758100p15823325.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
[sqlite] newB question; c++ and sqlite3; how2 check if word exist in the table
hi again, i wander how 2 check (efficiently) if some word (string, in c++) exist in the sqlite3 table any suggestions ? -- View this message in context: http://www.nabble.com/newB-question--c%2B%2B-and-sqlite3--how2-check-if-word-exist-in-the-table-tp15758100p15758100.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
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
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
[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
