[sqlite] how can we solve IF EXIST in SQLite
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') ); -- View this message in context: http://www.nabble.com/how-can-we-solve-IF-EXIST-in-SQLite-tp23828274p23828274.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
Re: [sqlite] how can we solve IF EXIST in SQLite
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
Re: [sqlite] how can we solve IF EXIST in SQLite
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 PKproduct_id FK quantitystock_date 1 10001028-05-2009 10001 1001 527-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 > > -- View this message in context: http://www.nabble.com/how-can-we-solve-IF-EXIST-in-SQLite-tp23828274p23830090.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
Re: [sqlite] how can we solve IF EXIST in SQLite
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 PKproduct_id FK quantitystock_date > 1 10001028-05-2009 > 10001 1001 527-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
Re: [sqlite] how can we solve IF EXIST in SQLite
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 To: General Discussion of SQLite Database 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 PKproduct_id FK quantitystock_date > 1 10001028-05-2009 > 10001 1001 527-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
Re: [sqlite] how can we solve IF EXIST in SQLite
On Tue, 2 Jun 2009 03:36:46 -0700 (PDT), 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 PKproduct_id FK quantitystock_date >1 10001028-05-2009 >10001 1001 527-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.. Pseudocode: BEGIN; UPDATE stock_tab SET . WHERE stock_id = 1; if sqlite_error() INSERT INTO stock_tab SET (...) VALUES (...); endif COMMIT; -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
Sorry, this was written down without testing. I see now that prod_batch_code must be the primary key, instead of stock_id, for the REPLACE to work as expected. Then some other expression must be used to fill stock_id, e.g. IF_NULL (s.stock_id, 29). I also see that this message crosses Kees Nuyt's idea which may be more comfortable if you like to keep the SQL simple.. Edzard --- edz...@volcanomail.com wrote: From: "Edzard Pasma" To: "General Discussion of SQLite Database" Cc: Subject: Re: [sqlite] how can we solve IF EXIST in SQLite Date: Tue, 2 Jun 2009 04:19:33 -0700 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 To: General Discussion of SQLite Database 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 PKproduct_id FK quantitystock_date > 1 10001028-05-2009 > 10001 1001 527-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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
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 > To: General Discussion of SQLite Database > 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 PKproduct_id FK quantitystock_date >> 1 10001028-05-2009 >> 10001 1001 527-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
Re: [sqlite] how can we solve IF EXIST in SQLite
guys i ll clarify the problem this is the purchase table here purchase id is PK purchase_id prod_batch_code vendor_type_code purchase_qty purchase_date --- --- --- 11000 1 100 2009-05-26 18:19:27 21001 1 100 2009-05-26 18:19:31 31002 1 100 2009-05-26 18:19:35 41003 1 100 2009-05-26 18:19:49 this is the stock table here stock_id is PK and prod_batch_code is FK stock_idprod_batch_code stock_qty stock_date -- --- -- --- 20001 1001 105 2009-05-26 18:19:27 20002 1002 100ps 2009-05-26 18:19:31 20003 1003 100ps 2009-05-26 18:19:35 20004 1003 100ps 2009-05-26 18:19:43 20005 1002 100ps 2009-05-26 18:19:44 20006 1001 100ps 2009-05-26 18:19:49 20007 1000 85 2009-05-26 18:19:50 20008 1000 85 2009-05-26 18:19:51 i wrote a trigger CREATE TRIGGER insert_stock_from_product AFTER INSERT ON purchase_tab BEGIN INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty, stock_date) values (new.purchase_id+2, new.prod_batch_code, new.purchase_qty, new.purchase_date ); END; instead of inserting the same products repeatedly in the stock table i jus want the quantity as well as the dates to be updated . and wen i insert a new product_batch_code to the purchase table its shuld be inserted in the stock table also... Edzard Pasma wrote: > > Sorry, this was written down without testing. I see now that > prod_batch_code must be the primary key, instead of stock_id, for the > REPLACE to work as expected. Then some other expression must be used to > fill stock_id, e.g. IF_NULL (s.stock_id, 29). I also see that this > message crosses Kees Nuyt's idea which may be more comfortable if you like > to keep the SQL simple.. > Edzard > > --- edz...@volcanomail.com wrote: > > From: "Edzard Pasma" > To: "General Discussion of SQLite Database" > Cc: > Subject: Re: [sqlite] how can we solve IF EXIST in SQLite > Date: Tue, 2 Jun 2009 04:19:33 -0700 > > 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 > To: General Discussion of SQLite Database > 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 PKproduct_id FK quantitystock_date >> 1 10001028-05-2009 >> 10001 1001 527-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 >&g
Re: [sqlite] how can we solve IF EXIST in SQLite
If you have unique index on stock_tab.prod_batch_code then you can re-write your trigger as this: INSERT OR REPLACE INTO stock_tab (stock_id, prod_batch_code, stock_qty, stock_date) SELECT new.purchase_id+2, new.prod_batch_code, new.purchase_qty + ifnull(b.stock_qty, 0), new.purchase_date FROM (SELECT new.prod_batch_code) a LEFT JOIN stock_tab b ON b.prod_batch_code = a.prod_batch_code Pavel On Tue, Jun 2, 2009 at 8:20 AM, robinsmathew wrote: > > guys i ll clarify the problem > this is the purchase table here purchase id is PK > > purchase_id prod_batch_code vendor_type_code purchase_qty purchase_date > --- --- > --- > 1 1000 1 100 2009-05-26 > 18:19:27 > 2 1001 1 100 2009-05-26 > 18:19:31 > 3 1002 1 100 2009-05-26 > 18:19:35 > 4 1003 1 100 2009-05-26 > 18:19:49 > > this is the stock table here stock_id is PK and prod_batch_code is FK > > stock_id prod_batch_code stock_qty stock_date > -- --- -- --- > 20001 1001 105 2009-05-26 18:19:27 > 20002 1002 100ps 2009-05-26 18:19:31 > 20003 1003 100ps 2009-05-26 18:19:35 > 20004 1003 100ps 2009-05-26 18:19:43 > 20005 1002 100ps 2009-05-26 18:19:44 > 20006 1001 100ps 2009-05-26 18:19:49 > 20007 1000 85 2009-05-26 18:19:50 > 20008 1000 85 2009-05-26 18:19:51 > > i wrote a trigger > CREATE TRIGGER insert_stock_from_product > AFTER INSERT ON purchase_tab > BEGIN > INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty, stock_date) > values (new.purchase_id+2, new.prod_batch_code, new.purchase_qty, > new.purchase_date ); > END; > > instead of inserting the same products repeatedly in the stock table i jus > want the quantity as well as the dates to be updated . and wen i insert a > new product_batch_code to the purchase table its shuld be inserted in the > stock table also... > > Edzard Pasma wrote: >> >> Sorry, this was written down without testing. I see now that >> prod_batch_code must be the primary key, instead of stock_id, for the >> REPLACE to work as expected. Then some other expression must be used to >> fill stock_id, e.g. IF_NULL (s.stock_id, 29). I also see that this >> message crosses Kees Nuyt's idea which may be more comfortable if you like >> to keep the SQL simple.. >> Edzard >> >> --- edz...@volcanomail.com wrote: >> >> From: "Edzard Pasma" >> To: "General Discussion of SQLite Database" >> Cc: >> Subject: Re: [sqlite] how can we solve IF EXIST in SQLite >> Date: Tue, 2 Jun 2009 04:19:33 -0700 >> >> 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 >> To: General Discussion of SQLite Database >> 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 >>> 1
Re: [sqlite] how can we solve IF EXIST in SQLite
If prod_batch_code is not a unique key (which is surprising as you may be updating more than one row), we can still write a pseudo INSERT OR REPLACE in the form of both an update and an insert statement. The update can go unchanged. The insert should not use values () but a query that only yields a row if the update failed: INSERT INTO stock_table (..) SELECT new.purchase_id+2, new.prod_batch_code, new.purchase_qty, new.purchase_date WHERE NOT EXISTS ( SELECT NULL FROM stock_table WHERE prod_batch_code = new.prod_batch_code); --- paiva...@gmail.com wrote: From: Pavel Ivanov To: General Discussion of SQLite Database Subject: Re: [sqlite] how can we solve IF EXIST in SQLite Date: Tue, 2 Jun 2009 09:01:08 -0400 If you have unique index on stock_tab.prod_batch_code then you can re-write your trigger as this: INSERT OR REPLACE INTO stock_tab (stock_id, prod_batch_code, stock_qty, stock_date) SELECT new.purchase_id+2, new.prod_batch_code, new.purchase_qty + ifnull(b.stock_qty, 0), new.purchase_date FROM (SELECT new.prod_batch_code) a LEFT JOIN stock_tab b ON b.prod_batch_code = a.prod_batch_code Pavel On Tue, Jun 2, 2009 at 8:20 AM, robinsmathew wrote: > > guys i ll clarify the problem > this is the purchase table here purchase id is PK > > purchase_id prod_batch_code vendor_type_code purchase_qty purchase_date > --- --- > --- > 1 1000 1 100 2009-05-26 > 18:19:27 > 2 1001 1 100 2009-05-26 > 18:19:31 > 3 1002 1 100 2009-05-26 > 18:19:35 > 4 1003 1 100 2009-05-26 > 18:19:49 > > this is the stock table here stock_id is PK and prod_batch_code is FK > > stock_id prod_batch_code stock_qty stock_date > -- --- -- --- > 20001 1001 105 2009-05-26 18:19:27 > 20002 1002 100ps 2009-05-26 18:19:31 > 20003 1003 100ps 2009-05-26 18:19:35 > 20004 1003 100ps 2009-05-26 18:19:43 > 20005 1002 100ps 2009-05-26 18:19:44 > 20006 1001 100ps 2009-05-26 18:19:49 > 20007 1000 85 2009-05-26 18:19:50 > 20008 1000 85 2009-05-26 18:19:51 > > i wrote a trigger > CREATE TRIGGER insert_stock_from_product > AFTER INSERT ON purchase_tab > BEGIN > INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty, stock_date) > values (new.purchase_id+2, new.prod_batch_code, new.purchase_qty, > new.purchase_date ); > END; > > instead of inserting the same products repeatedly in the stock table i jus > want the quantity as well as the dates to be updated . and wen i insert a > new product_batch_code to the purchase table its shuld be inserted in the > stock table also... > > Edzard Pasma wrote: >> >> Sorry, this was written down without testing. I see now that >> prod_batch_code must be the primary key, instead of stock_id, for the >> REPLACE to work as expected. Then some other expression must be used to >> fill stock_id, e.g. IF_NULL (s.stock_id, 29). I also see that this >> message crosses Kees Nuyt's idea which may be more comfortable if you like >> to keep the SQL simple.. >> Edzard >> >> --- edz...@volcanomail.com wrote: >> >> From: "Edzard Pasma" >> To: "General Discussion of SQLite Database" >> Cc: >> Subject: Re: [sqlite] how can we solve IF EXIST in SQLite >> Date: Tue, 2 Jun 2009 04:19:33 -0700 >> >> 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 >> To: General Discussion of SQLite Database >> 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
Re: [sqlite] how can we solve IF EXIST in SQLite
thanx a lot dude Pavel Ivanov-2 wrote: > > If you have unique index on stock_tab.prod_batch_code then you can > re-write your trigger as this: > > INSERT OR REPLACE INTO stock_tab > (stock_id, prod_batch_code, stock_qty, stock_date) > SELECT new.purchase_id+2, new.prod_batch_code, > new.purchase_qty + ifnull(b.stock_qty, 0), new.purchase_date >FROM (SELECT new.prod_batch_code) a > LEFT JOIN stock_tab b ON b.prod_batch_code = > a.prod_batch_code > > > Pavel > > On Tue, Jun 2, 2009 at 8:20 AM, robinsmathew > wrote: >> >> guys i ll clarify the problem >> this is the purchase table here purchase id is PK >> >> purchase_id prod_batch_code vendor_type_code purchase_qty >> purchase_date >> --- --- >> --- >> 1 1000 1 100 >> 2009-05-26 >> 18:19:27 >> 2 1001 1 100 >> 2009-05-26 >> 18:19:31 >> 3 1002 1 100 >> 2009-05-26 >> 18:19:35 >> 4 1003 1 100 >> 2009-05-26 >> 18:19:49 >> >> this is the stock table here stock_id is PK and prod_batch_code is FK >> >> stock_id prod_batch_code stock_qty stock_date >> -- --- -- --- >> 20001 1001 105 2009-05-26 18:19:27 >> 20002 1002 100ps 2009-05-26 18:19:31 >> 20003 1003 100ps 2009-05-26 18:19:35 >> 20004 1003 100ps 2009-05-26 18:19:43 >> 20005 1002 100ps 2009-05-26 18:19:44 >> 20006 1001 100ps 2009-05-26 18:19:49 >> 20007 1000 85 2009-05-26 18:19:50 >> 20008 1000 85 2009-05-26 18:19:51 >> >> i wrote a trigger >> CREATE TRIGGER insert_stock_from_product >> AFTER INSERT ON purchase_tab >> BEGIN >> INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty, >> stock_date) >> values (new.purchase_id+2, new.prod_batch_code, >> new.purchase_qty, >> new.purchase_date ); >> END; >> >> instead of inserting the same products repeatedly in the stock table i >> jus >> want the quantity as well as the dates to be updated . and wen i insert a >> new product_batch_code to the purchase table its shuld be inserted in the >> stock table also... >> >> Edzard Pasma wrote: >>> >>> Sorry, this was written down without testing. I see now that >>> prod_batch_code must be the primary key, instead of stock_id, for the >>> REPLACE to work as expected. Then some other expression must be used to >>> fill stock_id, e.g. IF_NULL (s.stock_id, 29). I also see that this >>> message crosses Kees Nuyt's idea which may be more comfortable if you >>> like >>> to keep the SQL simple.. >>> Edzard >>> >>> --- edz...@volcanomail.com wrote: >>> >>> From: "Edzard Pasma" >>> To: "General Discussion of SQLite Database" >>> Cc: >>> Subject: Re: [sqlite] how can we solve IF EXIST in SQLite >>> Date: Tue, 2 Jun 2009 04:19:33 -0700 >>> >>> 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 >>> To: General Discussion of SQLite Database >>> 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
Re: [sqlite] how can we solve IF EXIST in SQLite
its showing an error near "if": syntax error Kees Nuyt wrote: > > On Tue, 2 Jun 2009 03:36:46 -0700 (PDT), 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 PKproduct_id FK quantitystock_date >>1 10001028-05-2009 >>10001 1001 527-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.. > > Pseudocode: > BEGIN; > UPDATE stock_tab SET . WHERE stock_id = 1; > if sqlite_error() > INSERT INTO stock_tab SET (...) VALUES (...); > endif > COMMIT; > -- > ( Kees Nuyt > ) > c[_] > ___ > 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-tp23828274p23846618.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
Re: [sqlite] how can we solve IF EXIST in SQLite
you should use the insert or replace statement, it inserts if the row doesnt exist, if teh row does exists then it updates the row. --- On Wed, 6/3/09, robinsmathew wrote: From: robinsmathew Subject: Re: [sqlite] how can we solve IF EXIST in SQLite To: sqlite-users@sqlite.org Date: Wednesday, June 3, 2009, 3:15 AM its showing an error near "if": syntax error Kees Nuyt wrote: > > On Tue, 2 Jun 2009 03:36:46 -0700 (PDT), 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 >>1 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.. > > Pseudocode: > BEGIN; > UPDATE stock_tab SET . WHERE stock_id = 1; > if sqlite_error() > INSERT INTO stock_tab SET (...) VALUES (...); > endif > COMMIT; > -- > ( Kees Nuyt > ) > c[_] > ___ > 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-tp23828274p23846618.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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
On 3/06/2009 5:15 PM, robinsmathew wrote: > its showing an error near "if": syntax error "it", my crystal ball tells me, is an SQL processor, behaving much as expected when fed what looks like an "if" statement in some other language ... > Kees Nuyt wrote: >> Pseudocode: google("pseudocode") >> BEGIN; >> UPDATE stock_tab SET . WHERE stock_id = 1; >> if sqlite_error() >> INSERT INTO stock_tab SET (...) VALUES (...); >> endif >> COMMIT; Try this exploded version, written using functions in some arbitrary wrapper language, which you need to translate into whatever language you are using, supplying missing arguments like a connection and maybe a cursor, and adding error-checking where appropriate: exec_sql("BEGIN") exec_sql("UPDATE stock_tab SET . WHERE stock_id = 1") if an_error_happened(): exec_sql("INSERT INTO stock_tab SET (...) VALUES (...)") exec_sql("COMMIT") HTH, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
On Wed, 3 Jun 2009 00:42:53 -0700 (PDT), Harold Wood wrote: >you should use the insert or replace statement, >it inserts if the row doesnt exist, if the row >does exists then it updates the row. No, that doesn't fulfil the requirement, because quantity isn't incremented. >--- On Wed, 6/3/09, robinsmathew wrote: > > >From: robinsmathew >Subject: Re: [sqlite] how can we solve IF EXIST in SQLite >To: sqlite-users@sqlite.org >Date: Wednesday, June 3, 2009, 3:15 AM > > > >its showing an error near "if": syntax error > > >Kees Nuyt wrote: >> >> On Tue, 2 Jun 2009 03:36:46 -0700 (PDT), 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 >>>1 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.. >> >> Pseudocode: >> BEGIN; >> UPDATE stock_tab SET . WHERE stock_id = 1; >> if sqlite_error() >> INSERT INTO stock_tab SET (...) VALUES (...); >> endif >> COMMIT; >> -- >> ( Kees Nuyt -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
Select case when ((Select stock_id from Table where Stock_Id = ?) IS NULL) then insert into Table else update Table end; --- On Wed, 6/3/09, Kees Nuyt wrote: From: Kees Nuyt Subject: Re: [sqlite] how can we solve IF EXIST in SQLite To: sqlite-users@sqlite.org Date: Wednesday, June 3, 2009, 2:15 PM On Wed, 3 Jun 2009 00:42:53 -0700 (PDT), Harold Wood wrote: >you should use the insert or replace statement, >it inserts if the row doesnt exist, if the row >does exists then it updates the row. No, that doesn't fulfil the requirement, because quantity isn't incremented. >--- On Wed, 6/3/09, robinsmathew wrote: > > >From: robinsmathew >Subject: Re: [sqlite] how can we solve IF EXIST in SQLite >To: sqlite-users@sqlite.org >Date: Wednesday, June 3, 2009, 3:15 AM > > > >its showing an error near "if": syntax error > > >Kees Nuyt wrote: >> >> On Tue, 2 Jun 2009 03:36:46 -0700 (PDT), 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 >>>1 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.. >> >> Pseudocode: >> BEGIN; >> UPDATE stock_tab SET . WHERE stock_id = 1; >> if sqlite_error() >> INSERT INTO stock_tab SET (...) VALUES (...); >> endif >> COMMIT; >> -- >> ( Kees Nuyt -- ( Kees Nuyt ) c[_] ___ 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
Re: [sqlite] how can we solve IF EXIST in SQLite
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
Re: [sqlite] how can we solve IF EXIST in SQLite
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 wrote: From: BareFeet Subject: Re: [sqlite] how can we solve IF EXIST in SQLite To: "General Discussion of SQLite Database" 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
Re: [sqlite] how can we solve IF EXIST in SQLite
Hi Harold, > 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. Let me clarify. By "branching logic" I mean branching (eg if/then or loop) to perform an action such as update, insert, delete, create etc. The case/when/then construct is a function, not procedural branching (at least by my definition above). It will return different results depending on the test, but it can't be used to perform different actions based on the test. > you could modify this to meet the goal of insert x or update y. No, that won't work. You can't put an action (such as an update or an insert) inside a case statement. You can only put expressions (including select statements) within a case statement. 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
Re: [sqlite] how can we solve IF EXIST in SQLite
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 PKproduct_id FK quantitystock_date > 1 10001028-05-2009 > 10001 1001 527-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". >> When I saw this I though "What language is this? It's certainly not English." :-) It seems to me that robinsmathew should investigate the shift key, and the spell check functions in his email client. Is it just me, or do others find jibberish like "wat, jus, wanna, i, wen, etc..." to be very distracting and not the least bit "cool"? Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
Hi Dennis, > When I saw this I though "What language is this? It's certainly not > English." :-) > > It seems to me that robinsmathew should investigate the shift key, > and the spell check functions in his email client. Agreed. There were also missing characters from the "English" and SQL. A little proof reading goes a long way. > Is it just me, or do others find jibberish like "wat, jus, wanna, i, > wen, etc..." to be very distracting and not the least bit "cool"? Yes, very distracting, and inconsiderate form of communication, especially when asking for help. It takes far less time for one person to punctuate their own text than 200 readers to try to mentally insert punctuation after receiving. 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
Re: [sqlite] how can we solve IF EXIST in SQLite
Hmm, I have a view, its strictly a bunch of bit columns. Default value is 0=false, this view has a huge trigger on it. I use the different columns to activate particular sections of the trigger code, within those I do inserts, deletes, updates etc. It was a design around not having stored procedures. Harold Wood & Meyuni Gani -Original Message- From: BareFeet Sent: Wednesday, June 03, 2009 6:21 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] how can we solve IF EXIST in SQLite Hi Harold, > 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. Let me clarify. By "branching logic" I mean branching (eg if/then or loop) to perform an action such as update, insert, delete, create etc. The case/when/then construct is a function, not procedural branching (at least by my definition above). It will return different results depending on the test, but it can't be used to perform different actions based on the test. > you could modify this to meet the goal of insert x or update y. No, that won't work. You can't put an action (such as an update or an insert) inside a case statement. You can only put expressions (including select statements) within a case statement. 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
Re: [sqlite] how can we solve IF EXIST in SQLite
Hi, You might want to check-out StepSqlite PL/SQL compiler for SQLite at http://www.metatranz.com/stepsqlite Using it you can write the trigger (as part of a package body) almost the way you wrote in original post. StepSqlite compiles the PL/SQL code to a linux x86 shared library which can be linked in to your C++ app and used as the database interface to SQLite. There is an easy-to-follow tutorial here: http://www.metatranz.com/stepsqlite/tutorial.html *If you do not use C++*, wait till StepSqlite adds support for creating a loadable SQLite extension for SQLite and then you can simply load the compiled shared library into your SQLite database. (NOTE: below given is not pseudo code - this is actual code which you need to write and leave the rest to StepSqlite): === CREATE TABLE stock_tab(stock_id NUMBER(5), prod_batch_code NUMBER(5), stock_qty NUMBER(5), stock_date date); CREATE TABLE purchase_tab(product_batch_code NUMBER(5), purchase_qty NUMBER(5)); PACKAGE BODY MyDBinterface IS BEGIN CREATE TRIGGER insert_stock_from_product AFTER INSERT ON purchase_tab DECLARE count NUMBER(5); BEGIN SELECT count(prod_batch_code) INTO count FROM stock_tab WHERE prod_batch_code= :new.product_batch_code; IF count > 0 THEN UPDATE stock_tab SET stock_qty = stock_qty + :new.purchase_qty WHERE prod_batch_code= :new.product_batch_code ELSE INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty, stock_date) VALUES (20009, :new.product_batch_code, :new.purchase_qty, sysdate() ); END IF; END; END; === -SK On Tue, Jun 2, 2009 at 8:20 AM, robinsmathew wrote: > > guys i ll clarify the problem > this is the purchase table here purchase id is PK > > purchase_id prod_batch_code vendor_type_code purchase_qty purchase_date > --- --- > --- > 11000 1 100 2009-05-26 > 18:19:27 > 21001 1 100 2009-05-26 > 18:19:31 > 31002 1 100 2009-05-26 > 18:19:35 > 41003 1 100 2009-05-26 > 18:19:49 > > this is the stock table here stock_id is PK and prod_batch_code is FK > > stock_idprod_batch_code stock_qty stock_date > -- --- -- --- > 20001 1001 105 2009-05-26 18:19:27 > 20002 1002 100ps 2009-05-26 18:19:31 > 20003 1003 100ps 2009-05-26 18:19:35 > 20004 1003 100ps 2009-05-26 18:19:43 > 20005 1002 100ps 2009-05-26 18:19:44 > 20006 1001 100ps 2009-05-26 18:19:49 > 20007 1000 85 2009-05-26 18:19:50 > 20008 1000 85 2009-05-26 18:19:51 > > i wrote a trigger > CREATE TRIGGER insert_stock_from_product >AFTER INSERT ON purchase_tab >BEGIN > INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty, > stock_date) > values (new.purchase_id+2, new.prod_batch_code, > new.purchase_qty, > new.purchase_date ); >END; > > instead of inserting the same products repeatedly in the stock table i jus > want the quantity as well as the dates to be updated . and wen i insert a > new product_batch_code to the purchase table its shuld be inserted in the > stock table also... > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
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 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 FROMpurchase_tab WHERE prod_batch_code=1000) WHERE prod_batch_code=1000 AND EXISTS (SELECT prod_batch_code FROMstock_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 FROMstock_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
Re: [sqlite] how can we solve IF EXIST in SQLite
Sorry, I don't see EXISTS in SQLite documentation. On 8/20/09, Asif Lodhi 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 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 >FROMpurchase_tab >WHERE prod_batch_code=1000) > WHERE prod_batch_code=1000 > AND EXISTS (SELECT prod_batch_code > FROMstock_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 > FROMstock_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