Sorry, I don't see EXISTS in SQLite documentation.
On 8/20/09, Asif Lodhi <asif.lo...@gmail.com> wrote: > Hi, > > Perhaps you can do this in TWO SQL STATEMENTS - see below. However, > this way you inefficiently check the existence twice: > > On 6/2/09, robinsmathew <robinsmat...@hotmail.com> wrote: >> IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE >> prod_batch_code=1000) >> UPDATE stock_tab >> SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab >> WHERE >> oduct_batch_code=1000 ) >> WHERE prod_batch_code=1000 >> ELSE >> INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty, >> stock_date) values (20009, 1003, 200, >> DATETIME('NOW') ); > > UPDATE stock_tab > SET stock_qty=stock_qty+(SELECT purchase_qty > FROM purchase_tab > WHERE prod_batch_code=1000) > WHERE prod_batch_code=1000 > AND EXISTS (SELECT prod_batch_code > FROM stock_tab > WHERE prod_batch_code=1000); > INSERT INTO stock_tab (stock_id, prod_batch_code, stock_qty, stock_date) > VALUES (20009, 1003, 200, DATETIME('NOW') ) > WHERE NOT EXISTS (SELECT prod_batch_code > FROM stock_tab > WHERE prod_batch_code=1000); > > -Asif > PS: List users, is there any way we can cache the result of the EXISTS > clause above in order to avoid having to execute it twice using the > above approach? > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users