Solved

SQL Update some rows based off of filter troubles

Posted on 2007-12-03
5
180 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

685 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