CODEWITHSUNDEEP
mysqlsqlite
Muhammad Kashif Saeed
Muhammad Kashif Saeed

Reputation: 39

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

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!

Upvotes: 1

Views: 3580

Answers (4)

ScaisEdge
ScaisEdge

Reputation: 133380

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 
)

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13237

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

Upvotes: 1

Andrews B Anthony
Andrews B Anthony

Reputation: 1381

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

Upvotes: 0

Racil Hilan
Racil Hilan

Reputation: 25351

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

Upvotes: 0

Related Questions