Update query

[Mansoor Azam]

I have 2 tables

Create Table Orders (
OrderNo Long,
Amount Double )

Create Table OrderDetails (
OrderNo Long,
Item varchar(20),
ItemAmount Double )


the sum of ItemAmount for all items in an order in OrderDetails should be
equal to the Amount in Orders table.

What is the update query to set that for all rows in Orders?

[David Portas]

Re: Update query

The UPDATE would look like this:

UPDATE Orders
SET amount =
(SELECT SUM(itemamount)
FROM OrderDetails
WHERE orderno = Orders.orderno)


However, don't create a redundant amount column in your Orders table if you
can avoid it. If you put the calculated column in a view instead then you
won't need to update it at all. Something like this:

CREATE VIEW Orders_With_Amo unts (orderno, amount, ...)
AS
SELECT O.orderno, SUM(D.itemamoun t), ...
FROM Orders AS O
JOIN OrderDetails AS D
ON O.orderno = D.orderno
GROUP BY O.orderno

--
David Portas
SQL Server MVP
--

[Mansoor Azam]

Re: Update query

this doesnt work in MS Access. it gives message like 'Operation must be an
updatable query' etc
any other syntax? joins?

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:CcWdnY1bT_ X8Egbd4p2dnA@gi ganews.com...[color=blue]
> The UPDATE would look like this:
>
> UPDATE Orders
> SET amount =
> (SELECT SUM(itemamount)
> FROM OrderDetails
> WHERE orderno = Orders.orderno)
>
>
> However, don't create a redundant amount column in your Orders table if[/color]
you[color=blue]
> can avoid it. If you put the calculated column in a view instead then you
> won't need to update it at all. Something like this:
>
> CREATE VIEW Orders_With_Amo unts (orderno, amount, ...)
> AS
> SELECT O.orderno, SUM(D.itemamoun t), ...
> FROM Orders AS O
> JOIN OrderDetails AS D
> ON O.orderno = D.orderno
> GROUP BY O.orderno
>
> --
> David Portas
> SQL Server MVP
> --
>
>[/color]

[John Winterbottom]

Re: Update query

"Mansoor Azam" <mansoorb@shoa. net> wrote in message
news:2g1i7iF3fc 2rU1@uni-berlin.de...[color=blue]
> this doesnt work in MS Access. it gives message like 'Operation must be an
> updatable query' etc
> any other syntax? joins?
>[/color]

The reply was for sql server - you cross-posted to a sql server newsgroup.

In Access you can't use aggregates in a positioned update, so try something
like:

UPDATE Orders
SET amount =
DSum("itemamoun t", "OrderDetai ls", "orderno=" & [orderno])

if orderno is numeric data type, and

UPDATE Orders
SET amount =
DSum("itemaount ", "OrderDetai ls", "orderno='" & [orderno] & "'")

if it isn't.

But as David suggested, better not to store calculated amounts at all.