Posted by Ed Murphy on 12/17/06 16:37

info@bodykind.com wrote:

> 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.
>
> Any ideas?
>
> ----------------------------------------------------------------------------------------------------
> 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

Change
sum(OrdersProducts.Quantity)
to
coalesce(sum(OrdersProducts.Quantity),0)

Alternatively, append the WHERE EXISTS portion of Gert-Jan's query.

[Back to original message]