>I have 2 table in mysql database.
>
>1. Stock having field item_id, qty_stock
>2. purchase having field order_no,item_id, qty_purchase
>
>eg :
>Stock data : item_id qty_stock
> A001 0
> A002 0
>
>purchase data : order_no item_id qty_purchase
> 001 A001 10
> 001 A002 5
> 001 A001 3
>
>My Mysql syntax is
>
>"Update purchase a, stock b "+
> "set b.qty_stock = b.qty_stock + a.qty_purchase "+;
> "Where a.order_noi = '001' and a.item_id = b.item_id"
>
>The result is
> A001 3 (which should be 13)
> A002 5
>
>Thank you.

In SQL Server the query would be

update b
set qty_stock = b.qty_stock + a.qty_purchase
from stock b inner join (select order_no, item_id, sum(qty_purchase) as qty_purchase from purchase
group by order_id, item_id) a on a.order_id = b.order_id and a.item_id = b.item_id

You may want to adapt this syntax for MySQL.