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

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

----------------------------------------------------------------------------------------------------


Gert-Jan Strik wrote:
> The ANSI SQL compliant rewrite of this query would be:
>
> UPDATE Products
> SET Stock = (
> SELECT Products.Stock - OrdersProducts.Quantity
> FROM OrdersProducts
> WHERE OrdersProducts.ProductID = Products.ProductID
> AND OrdersProducts.OrderID=63116
> )
> WHERE EXISTS (
> SELECT *
> FROM OrdersProducts
> WHERE OrdersProducts.ProductID = Products.ProductID
> AND OrdersProducts.OrderID=63116
> )
>
> Since this is standard SQL, it will run on any SQL Server installation,
> and should run on any modern RDBMS. It might even run in MS Access.
>
> HTH,
> Gert-Jan
>
>
> "info@bodykind.com" wrote:
> >
> > The following query works fine in access and does exactly what I want
> > it to do however I get a syntax error when I port it over to SQL Server
> > 2000.
> >
> > -------------
> >
> > UPDATE OrdersProducts INNER JOIN Products ON OrdersProducts.ProductID =
> > Products.ProductID SET Products.Stock =
> > Products.Stock-OrdersProducts.Quantity
> > WHERE OrdersProducts.OrderID=63116
> >
> > --------------
> >
> > Any help would be greatly appreciated
> >
> > Many thanks

[Back to original message]