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
