Solved

SQL Update some rows based off of filter troubles

Posted on 2007-12-03
5
175 Views
Last Modified: 2010-03-20
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)

Open in new window

0
Comment
Question by:digital_slavery
  • 3
  • 2
5 Comments
 
LVL 27

Expert Comment

by:MikeToole
ID: 20395199
<the table has a single row for each item with a qty of 1>
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?
0
 
LVL 1

Author Comment

by:digital_slavery
ID: 20395264
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.
0
 
LVL 27

Accepted Solution

by:
MikeToole earned 500 total points
ID: 20395310
<The vendor column is the distributor_id >
<AND (COT_PO.distributor_id = @distributor_id) >
<however if all the items come from 1 vendor then it updates no problem>

Your problem is that you explicitly restrict the update to one vendor (=@distributor_id) in your SQL
0
 
LVL 1

Author Comment

by:digital_slavery
ID: 20395345
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.
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 20395774
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_ID, Item_ID, PO_ID, qtyReceived, ...)

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
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now