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 far as I know, you cannot do what you want to do in pure SQL.
However, perhaps someone cleverer can contradict me.
You could first execute the update statement, check if there was a row
which was updated using sqlite3_changes() (see
http://www.sqlite.org/capi3ref.html#sqlite3_changes), and then issue the
insert if there was none.
Martin
robinsmathew wrote:
> hey thanx for the reply... u leave the things happening inside.. wat i jus
> wanna do is i wanna insert a new row to a table
> the table will be like this
> stock_id PK product_id FK quantity stock_date
> 10000 1000 10 28-05-2009
> 10001 1001 5 27-05-2009
>
> and wen i insert a new row with values NULL, 1000, 15, 30-05-2009
>
> i dont want want it as a new recorde i jus want to update the first row coz
> its also having the same product id i jus want set the quantity = 10+15 and
> the date new date that is 30-05-2009
> and suppose if i insert row with different product_id it should be inserted
> as it is..
>
> Martin Engelschalk wrote:
>
>> Hi,
>>
>> what language is this? it certainly is not SQL or a "query".
>> I suspect that you can not use "insert or replace" (see
>> http://www.sqlite.org/lang_insert.html), because you look first for a
>> record with prod_batch_code=1000, and if you do not find it you insert
>> one with prod_batch_code = 1003.
>> S,. it seems to me that you have to implement the logic in your
>> application.
>>
>> Martin
>>
>> robinsmathew wrote:
>>
>>> hi am new to SQLite can anybody please tell me how this query can be
>>> solved
>>> in SQLite?
>>>
>>> IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE
>>> prod_batch_code=1000)
>>> UPDATE stock_tab
>>> SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab
>>> WHERE
>>> oduct_batch_code=1000 )
>>> WHERE prod_batch_code=1000
>>> ELSE
>>> INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
>>> stock_date) values (20009, 1003, 200,
>>> DATETIME('NOW') );
>>>
>>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
