thanx for ur reply dude.. but its showing an error " no such function: NULL_IF"
Edzard Pasma wrote: > > Hello, you are cleverer than you think. Your initial idea to use INSERT OR > REPLACE might look like: > > INSERT OR REPLACE INTO stock_tab(stock_id, prod_batch_code, stock_qty, > stock_date) > SELECT > s.stock_id, > p.prod_batch_code, > IF_NULL (s.stock_qty, 0) + p.purchase_qty > DATETIME('NOW') > FROM purchase_tab p > LEFT OUTER JOIN stock_tab s > ON s.prod_batch_code = p.prod_batch_code > WHERE p.product_batch_code=1000 > / > (assuming stock_id PRIMARY KEY) > > Best regards, Edzard > > --- engelsch...@codeswift.com wrote: > > From: Martin Engelschalk <engelsch...@codeswift.com> > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Subject: Re: [sqlite] how can we solve IF EXIST in SQLite > Date: Tue, 02 Jun 2009 12:46:58 +0200 > > Hi, > > as far as I know, you cannot do what you want to do in pure SQL. > However, perhaps someone cleverer can contradict me. > > You could first execute the update statement, check if there was a row > which was updated using sqlite3_changes() (see > http://www.sqlite.org/capi3ref.html#sqlite3_changes), and then issue the > insert if there was none. > > Martin > > robinsmathew wrote: >> hey thanx for the reply... u leave the things happening inside.. wat i >> jus >> wanna do is i wanna insert a new row to a table >> the table will be like this >> stock_id PK product_id FK quantity stock_date >> 10000 1000 10 28-05-2009 >> 10001 1001 5 27-05-2009 >> >> and wen i insert a new row with values NULL, 1000, 15, 30-05-2009 >> >> i dont want want it as a new recorde i jus want to update the first row >> coz >> its also having the same product id i jus want set the quantity = 10+15 >> and >> the date new date that is 30-05-2009 >> and suppose if i insert row with different product_id it should be >> inserted >> as it is.. >> >> Martin Engelschalk wrote: >> >>> Hi, >>> >>> what language is this? it certainly is not SQL or a "query". >>> I suspect that you can not use "insert or replace" (see >>> http://www.sqlite.org/lang_insert.html), because you look first for a >>> record with prod_batch_code=1000, and if you do not find it you insert >>> one with prod_batch_code = 1003. >>> S,. it seems to me that you have to implement the logic in your >>> application. >>> >>> Martin >>> >>> robinsmathew wrote: >>> >>>> 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') ); >>>> >>>> >>> _______________________________________________ >>> 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 > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/how-can-we-solve-IF-EXIST-in-SQLite-tp23828274p23830855.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users