SQL Server Query.....

[CzarJunkie]

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

Anyone got any ideas why this might be?

[Gazzy]

Does SQL Server allow inner joins as part of an update query? (new one on me if it does).

My first instinct would have been to write that query with the help of a subquery.

What is the error that query engine is throwing?

HTH,

G

[CzarJunkie]

Does SQL Server allow inner joins as part of an update query? (new one on me if it does).

My first instinct would have been to write that query with the help of a subquery.

What is the error that query engine is throwing?

HTH,

G

Thanks m8, that does appear to be the issue. the error is:

Incorrect syntax near the keyword 'INNER'

Any suggestions on a subquery or how we might come up with a solution?

[flipper321]

Try it this way....

UPDATE OrdersProducts

SET Products.Stock = Products.Stock-OrdersProducts.Quantity

FROM OrdersProducts

INNER JOIN Products ON OrdersProducts.ProductID = Products.ProductID

WHERE OrdersProducts.OrderID=63116

[flipper321]

In fact - if you are trying to update the Products table it should be more like...

UPDATE Products

SET Products.Stock = Products.Stock-OrdersProducts.Quantity

FROM Products

INNER JOIN OrdersProducts ON OrdersProducts.ProductID = Products.ProductID

WHERE OrdersProducts.OrderID=63116

[CzarJunkie]

Thanks Flipper, but I've already tried that.

It doesn't work properly when there are multiple ordersproducts records with the same productID - it only takes the first one and doesn't sum them up.

[flipper321]

Oh, you want them summed... that's a very different query...

Give me a minute...

[flipper321]

UPDATE Products

SET Products.Stock = Products.Stock - (select sum(OrdersProducts.Quantity)
from OrdersProducts
group by ordersproducts.ProductID
where ordersproducts.ProductID=products.ProductID and ordersproducts.orderid = 63116)

FROM Products

[CzarJunkie]

Thanks m8, but that hasn't worked, get the following error:

Incorrect syntax near the keyword 'where'.

And it set every Products.Stock field to Null

[Gazzy]

Hi Czar,

If you can PM me a rough schema for your SQL database in script form, and a few rows of data, I'll rasp something out for you.

Cheers,

G

[mysterym]

UPDATE Products

SET Products.Stock = Products.Stock - (select sum(OrdersProducts.Quantity)
from OrdersProducts
group by ordersproducts.ProductID
where ordersproducts.ProductID=products.ProductID and ordersproducts.orderid = 63116)

FROM Products

lol the group by and nested where clause have to be switched around:

Select ...
Where...
Group By..
Having..

Thats just off the top of my head.

[CzarJunkie]

Got it sorted over the weekend, thanks for your replies.