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 10000 100 2009-05-26
18:19:27
2 1001 10000 100 2009-05-26
18:19:31
3 1002 10000 100 2009-05-26
18:19:35
4 1003 10000 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+20000, 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
>
> --- [email protected] wrote:
>
> From: "Edzard Pasma" <[email protected]>
> To: "General Discussion of SQLite Database" <[email protected]>
> Cc: <[email protected]>
> 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
>
> --- [email protected] wrote:
>
> From: Martin Engelschalk <[email protected]>
> To: General Discussion of SQLite Database <[email protected]>
> 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
>>> [email protected]
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>>
>>
>>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> 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-tp23828274p23831505.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
