Group By and Update

I am new to SQL.
I need to do the following. Create a subtotal value from a table and then
populate another table with that value.
Table 1 consists of PO Orders.
I need to group the subtotals by Item Number.
Table 2 is our Item Master File. I would like to populate this table with
the subtotal derived above based on Item Number (the common field).
I have been successful running a group by Query on Table 1 to return the
results of Item Number with the subtotal value. However, I cannot figure out
the methodology to get that information into the second table.
Thanks.
AnnTry this:
UPDATE Table2
SET total = (SELECT SUM(x)
FROM Table1
WHERE Table1.item_num = Table2.item_num)
Are you sure you want to store the total? It's normally inefficient and
undesirable to store any derived calculations in the database. Have you
considered using a view instead? You have the option of an indexed view
to achieve much the same thing.
--
David Portas
SQL Server MVP
--|||I need to be able to access the data from Infopath. Therefore it must be in
a table. I believe is only for reference and is not consider a table. Am I
correct?
"David Portas" wrote:
> Try this:
> UPDATE Table2
> SET total => (SELECT SUM(x)
> FROM Table1
> WHERE Table1.item_num = Table2.item_num)
> Are you sure you want to store the total? It's normally inefficient and
> undesirable to store any derived calculations in the database. Have you
> considered using a view instead? You have the option of an indexed view
> to achieve much the same thing.
> --
> David Portas
> SQL Server MVP
> --
>|||You should be able to create a VIEW which will utilize the calculations and
have InfoPath pull data from the view.
Rick Sawtell
MCT, MCSD, MCDBA