How to subtract one table column values to the other table column values

Question

I have two tables Stock and Sale

Stock

id Name size Qty
1  box1  10  100
2  box2  12  200   
3  box3  14  500
4  box4  16  700

Sale

id Name size Qty
1  box1  10  1
2  box2  12  2  
3  box3  14  5
4  box4  16  7

I want this result after Subtract Qty

Stock

id Name size Qty
1  box1  10  99
2  box2  12  198  
3  box3  14  495
4  box4  16  693

Help!

What database are you using? MySQL and SQLite are not the same thing. — Gordon Linoff, Jun 23 '16 at 08:13

score 1 · Accepted Answer · edited May 23 '17 at 12:32

You can JOIN the tables by id and name then simply subtract the Qty values. Added table alias name for better readability

SELECT ST.id, ST.Name, ST.size, (ST.Qty - SA.Qty) AS Qty
FROM Stock ST
INNER JOIN Sale SA ON SA.id = ST.id AND SA.Name = ST.Name

If you though join by Name is not needed then you can join only by id as

SELECT ST.id, ST.Name, ST.size, (ST.Qty - SA.Qty) AS Qty
FROM Stock ST
INNER JOIN Sale SA ON SA.id = ST.id

Update for SQLite

UPDATE Stock 
SET Qty = ( SELECT ST.Qty - SA.Qty
            FROM Stock ST
            INNER JOIN Sale SA ON SA.id = ST.id )
WHERE id IN (SELECT id FROM Stock )

Got reference from this answer

ScaisEdge · Answer 2 · 2016-06-23T10:00:32.690

0

If you want store the result In mysql you can (using the name for relation) update

update Stock as a
inner join Sale as b on a.name = b.name 
set a.Qty = a.Qty -b.Qty

or using id for relation

update Stock as a
inner join Sale as b on a.id = b.id 
set a.Qty = a.Qty -b.Qty

or if you simply want a select you can

select a.id, a.name, a.size,  a.Qty -b.Qty as qty
from fromStock as a 
inner join Sale as b on a.id = b.id

In sqlite the update with inner join is not allowed so you use the subselect

update Stock 
set Qty = (    select Stock.Qty -Sale.Qty from Stock
                Inner join Sale on  Sale.name = Stock.name )
WHERE
EXISTS (
    SELECT *
    from Stock
                Inner join Sale on  Sale.name = Stock.name 
)

edited Jun 23 '16 at 10:00

answered Jun 23 '16 at 08:14

ScaisEdge

131,976
10
91
107

@10086 weel so .. you can easy get your result – ScaisEdge Jun 23 '16 at 08:17
how can i do in SQLite? – Muhammad Kashif Saeed Jun 23 '16 at 08:23

score 0 · Answer 3 · answered Jun 23 '16 at 08:17

0

Try this:

SELECT Stock.id, Stock.Name, Stock.size, (Stock.Qty - Sale.Qty) AS Qty
FROM Stock
INNER JOIN Sale ON Sale.id = Stock.id

answered Jun 23 '16 at 08:17

Racil Hilan

24,690
13
50
55

score 0 · Answer 4 · answered Jun 23 '16 at 08:58

0

Use the below query

Select table.id,table.name,table.size,sum(table.qty) from
(Select id,name,size,qty from stock 
Union
Select id,name,size,qty*-1 from sale) as table group by table.id,table.name,table.size

answered Jun 23 '16 at 08:58

Andrews B Anthony

1,381
9
27

How to subtract one table column values to the other table column values

4 Answers4