?
Solved

SQL Update some rows based off of filter troubles

Posted on 2007-12-03
5
Medium Priority
?
183 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

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. …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 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