Insert or Replace with sum of old values

Question

I am building Inventory Application using PhoneGap.In that i have one module STOCK for stock management.

Stock Table Query

CREATE TABLE STOCK (
    sto_id INTEGER PRIMARY KEY AUTOINCREMENT,
    pro_id INTEGER FOREIGNKEY REFERENCES PRODUCT(pro_id) UNIQUE,
    quantity TEXT
)

INSERT OR REPLACE Query

INSERT OR REPLACE INTO STOCK (pro_id,quantity) VALUES ("1","5");

There is not a single issue with this query its working perfectly but i want to update SUM of OLD VALUES WITH NEW ONE.

Example:

pro_id quantity   
1       5

This is existing record so now when i will fire above query for new transaction which have 3 quantity then quantity should be (5 (old) + 3 (new) ) = 8.

So after updateing record it looks like.

pro_id quantity   
1       8

How can i solve this any idea. or let me know if i am on wrong way. Thanks.

Answer 1

Actually I am not real founder of this solution. The real hero is Daverandom with the help of him i have solve my issue.

He has suggest me to follow this solution and that is really helpful to find my solution.

Older query looks like as

INSERT OR REPLACE INTO STOCK (pro_id,quantity) VALUES ("1","5"); 

New Query looks like as

INSERT OR IGNORE INTO STOCK (pro_id,quantity) VALUES (1,0) ; 
UPDATE STOCK SET quantity = quantity + 2 WHERE pro_id=1;

Update:

If you will not add WHERE pro_id="val" in UPDATE then it will UPDATE all rows. So that will generate will appropriate result.

When user fire query first time then quantity will be 2 and when you fire same query second time it will 4.

So, We can change that value in each update.

Again thanks to Daverandom.

Answer 2

In SQLite, you can create a trigger to handle such functionality:

CREATE TRIGGER t1 BEFORE INSERT ON STOCK WHEN NEW.pro_id IN (SELECT pro_id FROM STOCK) BEGIN
    UPDATE STOCK SET quantity=COALESCE(NEW.quantity, 0)+COALESCE((SELECT quantity FROM STOCK WHERE pro_id=NEW.pro_id), 0) WHERE pro_id=NEW.pro_id;
    SELECT RAISE(IGNORE); -- Ignore INSERT
END;

From now on, whenever you try to insert an existing pro_id, update on quantity is done instead. Conflict clause (OR REPLACE) doesn't matter as trigger will handle it (for pro_id).

Yet another, this time without triggers, using single statement solution:

INSERT OR REPLACE STOCK(pro_id, quantity)
    SELECT npro_id, COALESCE(nqty, 0)+COALESCE(quantity,0) FROM (
        SELECT 123 AS npro_id, 9999 AS nqty
    ) LEFT JOIN STOCK ON npro_id=pro_id;

Just replace 123 with new prod_id and 9999 with new quantity.