Posted by --CELKO-- on 12/17/06 17:02

>> Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. <<

This tells you something very important about your data, namely that
your OrdersProducts or Products table or both have several occurences
of the same product_id in this order. Run the query by itself and find
the problem.

SELECT P.product_id
FROM OrdersProducts AS OP, Products AS P
WHERE OP.product_id = P.product_id
AND OP.order_id = 63116
GROUP BY P.product_id
HAVING COUNT(*) > 1;

Since you did nopt post DDL, nobody can help any further. Oh, in case
some suggests using an UPDATE.. FROM.. syntax do not do it. That will
grab a random row from the subquery and use it -- totally scrambling
your database.

As a bit of education, you need to look up a good book on SQL to see
how the language really works. ACCESS is so far from SQL that it takes
a year to un-learn it; until then you are in danger writing SQL that
runs -- BUT not the way you meant it to.

I cleaned up your vague data element names and modified your original
ACCESS statement:

UPDATE (OrdersProducts AS OP
INNER JOIN
Products AS P
ON OP.product_id = P.product_id) AS Foobar
SET P.Stock = (P.stock_qty - OP.order_qty)
WHERE OP.order_id = 63116;

What a subquery expression in Standard SQL says is that we materialize
a working table, named Foobar in this example. If this syntax were
valid and consistent, then the UPDATE would apply to Foobar, not any of
the base tables!! Then the materialized table would disappear.
Nothing would have happened.

An UPDATE statement works on one and only one base table (or an
updatable VIEW that resolves to a single base table). If you could do
more than one table, then you would update the same rows multiple times
from the various JOINs.

[Back to original message]