SQL Update some rows based off of filter troubles
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?
UPDATE InventoryMaster
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)
ASKER
The reason the table holds a row for each item is b/c we have several steps in our "special ording process". Step1 create a list of parts that need to be ordered, (COT) that are not in stock at wherehouse. Step 2 from step 1 a purchase order is created for each vendor( each COT can have mulitple POs). Step 3 add a shipping record to each purchase order, (puchase orders may have mulitple shippments). step 4 receive the shipments. During this process because some items need to be in different POs and shippments the only way I found to track that was to break down everything so that they could be managed on an individual basis. We need to add and subtrac items as they move through this process so the qty columns we used to do the math. The vendor column is the distributor_id and cot_id is the pri key for the items being ordered that were not in the wherehouse. Each step in the process works like this: Create a COT coversheet with details. Add items to the COT. Then create a PO Coversheet for each different vendor, then add/edit items for the PO. Create a shipping coversheet and add items from the PO that will be shipped.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yeah thats what the problem was, I figured it out by changing the filter of distributor_id to use our internal inventory table that also has the distributor_id as one of the fk in the table and everything is working as it is supposed. an easy 500 points for you my friend. However I would like to get your feedback about this table and the way I have it setup from my previous comments.
My thoughts are these:
For the COT you need two tables:
tblCOT(Cot_ID, ... .)
tblCOTParts(Cot_ID, Item_ID, QtyRequired, ... .)
Similarly for the Purchase Orders
tblPO(PO_ID, Distributor_ID, Cot_ID, ... ) -- assuming that a PO never addresses >1 COT
tblPOParts(PO_ID, Item_ID, QtyRequired, ... )
tblShipment(Shipment_ID, Distributer_ID, Date_received...) -- PO not put here in case a shipment might cover > 1 PO
tblShipmentParts(Shipment_
Using this sort of structure you could construct a Select statement that returns the sum of qty received for each item_id on a COT.
Hope this helps,
Mike
For the COT you need two tables:
tblCOT(Cot_ID, ... .)
tblCOTParts(Cot_ID, Item_ID, QtyRequired, ... .)
Similarly for the Purchase Orders
tblPO(PO_ID, Distributor_ID, Cot_ID, ... ) -- assuming that a PO never addresses >1 COT
tblPOParts(PO_ID, Item_ID, QtyRequired, ... )
tblShipment(Shipment_ID, Distributer_ID, Date_received...) -- PO not put here in case a shipment might cover > 1 PO
tblShipmentParts(Shipment_
Using this sort of structure you could construct a Select statement that returns the sum of qty received for each item_id on a COT.
Hope this helps,
Mike
First reaction: Why?
If the existence of a row indicates an order for qty = 1 then the quantity column is redundant.
What is the name of the vendor column? What do cot_id and distributor_id signify?