Dayanand Dayanand - 7 months ago 24
SQL Question

Update table in Sql

tblProductStock

ID Name Qyt
1 X 50
2 Y 40
3 Z 30


tblStockMinus

Id Name Qty
1 X 10
1 X 20
2 Y 30
3 Z 20


I want to update tblProductStock. and output will be following.

tblProductStock

ID Name Qyt
1 X 20
2 Y 10
3 Z 10

Answer

This will give you required result.

select t1.id, t1.qty-t2.qty as qty from tblProductStock as t1 inner join
(
select id, sum(qty) as qty from tblStockMinus group by id
) as t2 on t1.id=t2.id

You need to update this (but it depends in the RDBMS. This is for MS SQL Server)

Update t
set
t.qty=t3.qty from tblProductStock as t inner join
(
    select t1.id, t1.qty-t2.qty as qty from tblProductStock as t1 inner join
    (
    select id, sum(qty) as qty from tblStockMinus group by id
    ) as t2 on t1.id=t2.id
) as t3 on t.id=t3.id