Posted by Gert-Jan Strik on 12/14/06 19:27

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]