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+20000, 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);
--- [email protected] wrote:
From: Pavel Ivanov <[email protected]>
To: General Discussion of SQLite Database <[email protected]>
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+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 <[email protected]> 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
>>
>> --- [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
>
_______________________________________________
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
