On 29 Jul 2013, at 12:57pm, Navaneeth.K.N <navaneet...@gmail.com> wrote:
> When inserting a word, first I check if the words exists by performing > a "select" query. If it exists, I fire an update query to increment > the confidence for that word. If word is not available, I fire an > insert query to insert the word. > > In both the cases, I can't skip doing two queries. One for checking > existence and second for updating or creating. I am wondering is there > an easy way to solve this by just doing one query? I have tried > "insert or replace", but I can't use that as it changes the rowid's. If you have defined your tables correctly, the rowid would not change, the INSERT would just fail. You could define the 'word' column as UNIQUE, or once our table is created create an index which forces the 'word' column to be unique. Then inserting another row with the same 'word' would fail. Then your program to increment a word could would do something like INSERT OR IGNORE a new row with the correct 'word' and a confidence of 0 UPDATE the row with that word to increment the confidence. Since the table is set up not to allow duplication, if the row already exists the 'INSERT' will fail, but since you used 'INSERT OR IGNORE' your program would IGNORE the failure and carry on regardless, incrementing the existing confidence. If that solution doesn't work for you you might like to try first doing UPDATE myTable SET ... and then looking at the result of sqlite3_changes() to see whether it is 1 or not. If it's zero, then you insert a new row, with a confidence of 1. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
