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+20000, 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 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
>>>
>>> --- 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
>>>
>>>
>>
>> --
>> 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
>> 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-tp23828274p23834137.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
