Posted by info@bodykind.com on 12/17/06 22:28

Erland, your SQL worked perfectly. Have tested it on several different
orders and works fine. I only want the solution to work in SQL Server
anyway so it's not a problem if it won't work in access.

For your information there are supposed to be multiple records in the
ordersproducts table with the same productid as we have products which
have different colours which are recorded in a seperate table. We don't
keep a record of the stock of each individual colour just the total
number in stock.

Once again, many thanks to everyone who has contributed, I would never
have found that solution on my own.


Erland Sommarskog wrote:
> info@bodykind.com (info@bodykind.com) writes:
> > The following query appears to work correctly in SQL server, the only
> > problem (and it is a major problem) is that it sets the value of the
> > stock field to null on every record except the one that it is reducing.
>
> That's rude.
>
> > --------------------------------------------------------------------------
> > --------------------------
> > UPDATE Products
> >
> > SET Products.Stock = Products.Stock -
> >
> > (
> > select sum(OrdersProducts.Quantity)
> > from OrdersProducts
> > where ordersproducts.ProductID=products.ProductID and
> > ordersproducts.orderid = 63116 group by ordersproducts.ProductID
> > )
> >
> > FROM Products
>
> Since there is no WHERE clause to restrict the update, all rows are
> updated.
>
> It may work better if you attach Gert-Jan's WHERE clause:
>
> >> WHERE EXISTS (
> >> SELECT *
> >> FROM OrdersProducts
> >> WHERE OrdersProducts.ProductID = Products.ProductID
> >> AND OrdersProducts.OrderID=63116
> >> )
>
> It's interesting that Gert-Jan's query failed with "Subquery returned more
> than 1 value." That would indicate that your Access query is not correct
> either. Then again, I don't know Access or Jet, so maybe it does what
> you want to after all.
>
> Here is is yet one more suggestion:
>
> UPDATE Products
> SET Stock = P.Stock - OP.Quantity
> FROM Products P
> JOIN (SELECT ProductID, Quantity = SUM(Quantity)
> FROM OrderProducts
> WHERE OrderID = 63116
> GROUP BY ProductID) AS OP ON P.ProductID = OP.ProductID
>
> This syntax is my personal preference, in terms of simplicity and under-
> standability. It is also likely to be the best performancewise. But it
> has the disctinct advantage that it only works on SQL Server, so if you
> want a solution that works both on Access and SQL Server, this is not
> for you.
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]