Help Setting Sum of Column

SQL Apprentice Question
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


Celko Answers
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.);



>> I need to update the Item_Inv table and set the max_qty column to represent the total sale_qty for each location_id, Item_no in the Items_sold table. <<


Do you know the rule in RDBMS about not storing computed data? It is
redundant and such computations should be done with a VIEW.


>> So I'll have multiple rows of Items_sold for a location/item combo. The


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.