SQL does have branching logic. (SELECT CASE WHEN ((SELECT StoreId From History WHERE ItemId = NEW.ID LIMIT 1) IS NULL) THEN 0 ELSE (SELECT StoreId FROM History WHERE ItemId = NEW.ID AND UnitPrice = (SELECT MIN(UnitPrice) FROM HISTORY WHERE ItemId = NEW.ID)) END);
i use it in my current project. you could modify this to meet the goal of insert x or update y. Woody --- On Wed, 6/3/09, BareFeet <list....@tandb.com.au> wrote: From: BareFeet <list....@tandb.com.au> Subject: Re: [sqlite] how can we solve IF EXIST in SQLite To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Date: Wednesday, June 3, 2009, 8:29 PM 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users