Posted by Erland Sommarskog on 12/17/06 18:05

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]