INSERT OR IGNORE INTO table (word, confidence) VALUES (:word, :initialconfidence - :confidenceincrement); UPDATE table SET confidence=confidence+:confidenceincrement WHERE word=:word;
Still two statements but does not require application "help" and the rowid is stable ... Assuming that :initialconfidence and :confidenceincrement are constant, you could put the two statements in an instead of trigger on the table. Then your code would only need to execute a single statement ... > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Navaneeth.K.N > Sent: Monday, 29 July, 2013 05:58 > To: General Discussion of SQLite Database > Subject: [sqlite] Query optimization: Checking for existence before > performing action > > Hello, > > I am trying to optimize the SQL calls that my application makes. I > have a scenario where words are inserted into a table. Now each word > will have a column called "confidence". There is a unique primary key > on "word". > > 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. > > Any help would be great! > > -- > Thanks > Navaneeth > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
