Help Setting Sum of Column

This seems like this should be easy but I'm struggling. I have an
Items_Sold table with columns for Loc_id, Item_no, Qty_sold, and
Date_sold. I also have an Item_Inv table with columns of Loc_id,
Item_no, Max_qty, and many other columns. I need to update the
Item_Inv table and set the Max_qty column to represent the total
Qty_sold for each Loc_id, Item_no in the Items_sold table. So I'll
have multiple rows of Items_sold for a location/item combo. The
Max_qty column in the Item_Inv table should equal to sum of all the
Qty_sold columns for that location/item combo. Hopefully that makes
sense. Thanks.UPDATE Item_Inv
SET Max_qty = COALESCE(
(select sum(Qty_sold) from Items_Sold as S
where Item_Inv.Loc_id = S.Loc_id
and Item_Inv.Item_no = S.Item_no), 0)
Note that the COALESCE prevents Max_qty from being assigned a NULL
when there is no matching (Loc_id, Item_no).
Roy Harvey
Beacon Falls, CT
On 28 Apr 2006 15:27:42 -0700, tony@.acslhome.com wrote:

>This seems like this should be easy but I'm struggling. I have an
>Items_Sold table with columns for Loc_id, Item_no, Qty_sold, and
>Date_sold. I also have an Item_Inv table with columns of Loc_id,
>Item_no, Max_qty, and many other columns. I need to update the
>Item_Inv table and set the Max_qty column to represent the total
>Qty_sold for each Loc_id, Item_no in the Items_sold table. So I'll
>have multiple rows of Items_sold for a location/item combo. The
>Max_qty column in the Item_Inv table should equal to sum of all the
>Qty_sold columns for that location/item combo. Hopefully that makes
>sense. Thanks.|||Thanks very much, Roy. It's perfect.
-Tony
Roy Harvey wrote:
> UPDATE Item_Inv
> SET Max_qty = COALESCE(
> (select sum(Qty_sold) from Items_Sold as S
> where Item_Inv.Loc_id = S.Loc_id
> and Item_Inv.Item_no = S.Item_no), 0)
> Note that the COALESCE prevents Max_qty from being assigned a NULL
> when there is no matching (Loc_id, Item_no).
> Roy Harvey
> Beacon Falls, CT
> On 28 Apr 2006 15:27:42 -0700, tony@.acslhome.com wrote:
>|||Here is a guess as to the DDL you did not bother to post.
CREATE TABLE Sales
(location_id INTEGER NOT NULL,
item_nbr INTEGER NOT NULL,
FOREIGN KEY (location_id, item_nbr)
REFERENCES Inventory (location_id, item_nbr),
sale_qty INTEGER NOT NULL,
sale_date DATETIME NOT NULL,
PRIMARY KEY (location_id, item_nbr, sale_date));
CREATE TABLE Inventory
(location_id INTEGER NOT NULL,
item_nbr INTEGER NOT NULL,
PRIMARY KEY (location_id, item_nbr),
max_qty INTEGER NOT NULL, -- computable!!
etc.);
Do you know the rule in RDBMS about not storing computed data? It is
redundant and such computations should be done with a VIEW.
max_qty column in the Inventory table should equal to sum of all the
sales_qty columns for that location/item combo. <<
CREATE VIEW (location_id, item_nbr, max_qty)
AS
SELECT location_id, item_nbr, SUM(sales_qty)
FROM Sales
GROUP BY location_id, item_nbr;
I think that you are thinking in terms an old file system in which the
Sales would be in one file and then merged into the Inventory file in a
batch. In an RDBMS, the tables are part of the WHOLE schema, and not
disjoint islands of data, like a file system. Think in relational
terms, not sequential processing and tape file merging.