I have two tables with Product and Purchase
'Product Table:
- ProductID = 1
PName = tv
StockQty = 10
Purchase Table: -
PurchaseID =1
PID=1
PurchaseQty = 5 '
Product ID is Foreign Key in Purchase table.I am trying to write store proc so that whenever Purchase Quantity in added in purchase table.The main table product should also increment . For example: I add purchase quantity 5 .When I refresh product table it should display 15.I am getting only 10 in Product and whatever added in purchase.Please advise.
Below is my store proc
'CREATE PROCEDURE insertpurchase
@PID int ,
@PurchaseQty int,
@StockQty int
AS
BEGIN
INSERT INTO [dbo].[Purchase] (PID ,PurchaseQty ) VALUES (@PID ,@PurchaseQty ) UPDATE [dbo].[Product] SET Product.StockQty = Product.StockQty + Purchase.PurchaseQty from Purchase END ' Can somebody guide what is wring with my query ?