On 29 July 2013 12:57, Navaneeth.K.N <navaneet...@gmail.com> wrote: > 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!
Something like: SQLite version 3.7.17 2013-05-20 00:56:22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> sqlite> create table words( id text unique, confidence integer default 0 ); sqlite> create trigger words_before_insert before insert on words ...> begin update words set confidence=confidence+1 where id=new.id; ...> end; sqlite> sqlite> select * from words; sqlite> sqlite> insert or ignore into words( id ) values( 'fred' ); sqlite> insert or ignore into words( id ) values( 'bill' ); sqlite> insert or ignore into words( id ) values( 'joe' ); sqlite> insert or ignore into words( id ) values( 'fred' ); sqlite> insert or ignore into words( id ) values( 'joe' ); sqlite> insert or ignore into words( id ) values( 'fred' ); sqlite> insert or ignore into words( id ) values( 'fred' ); sqlite> insert or ignore into words( id ) values( 'fred' ); sqlite> sqlite> select * from words; fred|4 bill|0 joe|1 sqlite> > > -- > Thanks > Navaneeth Regards, Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
