Hi Mathew,
> hi am new to SQLite can anybody please tell me how this query can be
> solved
> in SQLite?
>
> 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') );
Your if/then/else structure is a branching procedure. SQL is a
language for manipulating sets and so does not facilitate procedural
branching such as if/then/else or loops.
SQL, being a language dealing with sets, is designed to perform
actions on entire sets or subsets of data.
So, instead of saying "test this, branch here if true, there if
false", you need to instead say "do this to the subset that tests
true, and do that to the subset that tests false".
So, something like this:
begin immediate
;
update Stock_Tab
set Stock_Qty = Stock_Qty +
(select Purchase_Qty from Purchase_Tab where Product_batch_code = 1000)
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 1 from Stock_Tab where Prod_Batch_Code = 1000)
;
commit
;
Tom
BareFeet
--
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
