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]

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_Amounts (orderno, amount, ...)
AS
SELECT O.orderno, SUM(D.itemamount), ...
FROM Orders AS O
JOIN OrderDetails AS D
ON O.orderno = D.orderno
GROUP BY O.orderno

--
David Portas
SQL Server MVP
--

[Mansoor Azam]

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

"David Portas" <RE****************************@acm.org> wrote in message
news:Cc********************@giganews.com...

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_Amounts (orderno, amount, ...)
AS
SELECT O.orderno, SUM(D.itemamount), ...
FROM Orders AS O
JOIN OrderDetails AS D
ON O.orderno = D.orderno
GROUP BY O.orderno

--
David Portas
SQL Server MVP
--

[John Winterbottom]

"Mansoor Azam" <ma******@shoa.net> wrote in message
news:2g************@uni-berlin.de...

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

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("itemamount", "OrderDetails", "orderno=" & [orderno])

if orderno is numeric data type, and

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

if it isn't.

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