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
