Updating more than one record with different values 1

[anet]

I need some help updating the quantity on hand in an inventory table with information retrieved from two other tables.

There are three tables involved: Product, Inventory, and Ingredient. The ingredient table is the junction between product and inventory.

What the stored procedure is attempting to do is add a line item from a customer order (this part works fine) and then update the inventory table to subtract the ingredients used (which are tracked by weight). For example a small pizza would contain a specific amount of dough, cheese, sauce, etc.

What is happening is that the stored procedure is updating each appropriate line in the inventory table, but only by the weight of the last product that was retreived in the "select @weight = weight" line. How do we make sure the inventory quantity is reduced by the correct weight for the product?

Here is the stored procedure we are using:

CREATE procedure sp_AddLineItem2 (@oid int,
@prodcd char(5),
@qty float
)
As

declare @localprice money
declare @weight float
declare @inv int
select @localprice=price from cheesy_product where PK_ProductID = @prodcd
select @weight=weight, @inv=FK_invtID from cheesy_Ingredient where FK_ProductID = @prodcd
Begin
Update cheesy_Inventory set quantity = quantity - @weight where PK_InvtID IN(select
FK_invtID from cheesy_ingredient where FK_productID = @prodcd)
insert into Cheesy_lineitems (FK_ProductID, FK_CustOrderID, quantity, price) values
(@prodcd,@oid, @qty,@localprice)

End
if exists (select * from cheesy_lineitems where FK_CustOrderID = @oid and FK_ProductID = @prodcd)
return 10
else
return -1

 

[adam0101]

I think this should work:

CREATE procedure sp_AddLineItem2 (@oid int, 
                                  @prodcd char(5),
                                  @qty float 
                                  )
As

Update cheesy_Inventory set quantity = quantity - (select Max(weight) from cheesy_Ingredient where FK_ProductID = @prodcd)

insert into Cheesy_lineitems (FK_ProductID, FK_CustOrderID, quantity, price) SELECT @prodcd,@oid,@qty,price from cheesy_product where PK_ProductID = @prodcd


if exists (select * from cheesy_lineitems where FK_CustOrderID = @oid and FK_ProductID = @prodcd)
  return 10
else
  return -1

 

[adam0101]

Wow, I screwed that up. Don't use the code I just posted. It'll Update every inventory item. What I wanted to do was subtract the ingredient (via subquery) from the inventory where the ingredient is in the product and in the correct inventory item, then repeat this for all inventory items that have the ingredients of the selected product. The code below should be a little closer to what you can use. At the very least, maybe it'll give you an idea of how to write code that does what you want.

CREATE procedure sp_AddLineItem2 (@oid int, 
                                  @prodcd char(5),
                                  @qty float 
                                  )
As

Update cheesy_Inventory set quantity = quantity - (select weight from cheesy_Ingredient tbaIngredients where FK_ProductID = @prodcd AND tbaIngredients.FK_invtID = cheesy_Inventory.PK_InvtID) where PK_InvtID IN(select FK_invtID from cheesy_ingredient where FK_productID = @prodcd)

insert into Cheesy_lineitems (FK_ProductID, FK_CustOrderID, quantity, price) SELECT @prodcd,@oid,@qty,price from cheesy_product where PK_ProductID = @prodcd


if exists (select * from cheesy_lineitems where FK_CustOrderID = @oid and FK_ProductID = @prodcd)
  return 10
else
  return -1

 

[anet]

It works. Thank you so much!!!