Re: [sqlite] how can we solve IF EXIST in SQLite

[Asif Lodhi]

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

Re: [sqlite] how can we solve IF EXIST in SQLite

[Asif Lodhi]

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

[sub sk79]

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

[Harold Wood & Meyuni Gani]

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 <list@tandb.com.au>
Sent: Wednesday, June 03, 2009 6:21 PM
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
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

[BareFeet]

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

[Dennis Cote]

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

[BareFeet]

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

[Harold Wood]

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

Re: [sqlite] how can we solve IF EXIST in SQLite

[BareFeet]

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

[Harold Wood]

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 <k.n...@zonnet.nl> wrote:


From: Kees Nuyt <k.n...@zonnet.nl>
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
<hwoody2w...@yahoo.com> 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 <robinsmat...@hotmail.com> wrote:
>
>
>From: robinsmathew <robinsmat...@hotmail.com>
>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
>> <robinsmat...@hotmail.com> 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

[Kees Nuyt]

On Wed, 3 Jun 2009 00:42:53 -0700 (PDT), Harold Wood
<hwoody2w...@yahoo.com> 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 <robinsmat...@hotmail.com> wrote:
>
>
>From: robinsmathew <robinsmat...@hotmail.com>
>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
>> <robinsmat...@hotmail.com> 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

[John Machin]

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

[Harold Wood]

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 <robinsmat...@hotmail.com> wrote:


From: robinsmathew <robinsmat...@hotmail.com>
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
> <robinsmat...@hotmail.com> 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

[robinsmathew]

its showing an error near "if": syntax error


Kees Nuyt wrote:
> 
> On Tue, 2 Jun 2009 03:36:46 -0700 (PDT), robinsmathew
> <robinsmat...@hotmail.com> 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

[robinsmathew]

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 <robinsmat...@hotmail.com>
> 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, 200009). 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" <edz...@volcanomail.com>
>>> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>>> Cc: <sqlite-users@sqlite.org>
>>> 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 <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 f

Re: [sqlite] how can we solve IF EXIST in SQLite

[Edzard Pasma]

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 <paiva...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
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 <robinsmat...@hotmail.com> 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" <edz...@volcanomail.com>
>> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>> Cc: <sqlite-users@sqlite.org>
>> 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 <engelsch...@codeswift.com>
>> To: Gen

Re: [sqlite] how can we solve IF EXIST in SQLite

[Pavel Ivanov]

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 <robinsmat...@hotmail.com> 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" <edz...@volcanomail.com>
>> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>> Cc: <sqlite-users@sqlite.org>
>> 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 <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 wan

Re: [sqlite] how can we solve IF EXIST in SQLite

[robinsmathew]

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" <edz...@volcanomail.com>
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Cc: <sqlite-users@sqlite.org>
> 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 <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 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 y

Re: [sqlite] how can we solve IF EXIST in SQLite

[robinsmathew]

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 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

[Edzard Pasma]

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" <edz...@volcanomail.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Cc: <sqlite-users@sqlite.org>
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 <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 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

[Kees Nuyt]

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

[Edzard Pasma]

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 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

[Martin Engelschalk]

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

[robinsmathew]

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

[Martin Engelschalk]

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] how can we solve IF EXIST in SQLite

[robinsmathew]

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