I have a table that captures our inventory, the table has a single row for each item with a qty of 1, so if we have 3 parts ordered and each part has a qty of 5 then 15 rows are created in the table. When a user needs to order items they create a new purchase order and then are able to see the items grouped by vendor, so out of our 15 items 5 might come from 1 vendor and 10 from another one. My issue is that when an order is split up like this my update to the table fails, however if all the items come from 1 vendor then it updates no problem. I think the issue for it failing to update is b/c not all rows are being updated. But I am not sure. Any ideas on how I can resolve this?
SET po_id = @po_id, qtyPO = 1, goingTo = @destination_id
FROM InventoryMaster INNER JOIN
COT_PO ON InventoryMaster.po_id = COT_PO.po_id
WHERE (InventoryMaster.cot_id = @cot_id) AND (COT_PO.distributor_id = @distributor_id) AND (COT_PO.po_id = @po_id)