Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Update some rows based off of filter troubles

Posted on 2007-12-03
5
Medium Priority
?
184 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

618 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