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