Solved Update as select not updateable (1 Viewer)

ELiedeker

Member
Local time
Today, 02:42
Joined
Sep 22, 2020
Messages
33
Hi, I have a relatively simple query I'm trying to run to update inventory with the sum of existing inventory, plus new items ordered. But I'm getting an error that my query isn't updateable, and I'm not sure what's wrong with it. In Oracle, you can update a column with the results of a calculation. Can you not do this in Access, or is there another problem with my query?

Update Supplies set InStock = (
SELECT InStock + Qty
FROM Supplies, Orders
WHERE Orders.IsComplete = True
AND Orders.InventoryUpdated = False
AND Supplies.SupplyID = Orders.SupplyID);

Thanks in advance!

Eileen
 

Minty

AWF VIP
Local time
Today, 08:42
Joined
Jul 26, 2013
Messages
8,760
That will set every InStock record in your table to the same value.
There are no criteria for the table you are trying to update.

And you shouldn't really try and maintain stock levels in this way, you should calculate them from a transaction-based set of records.
 

ELiedeker

Member
Local time
Today, 02:42
Joined
Sep 22, 2020
Messages
33
Holy cow, you're right. I didn't constrain the outer query.🤦‍♀️

Regarding your other comment, I plan to perform this update upon closing an Order Form. I don't want to update inventory until I'm sure they're done ordering. So, I'm keeping track of whether orders are pending or complete, and then updating inventory when finished -- thus the "InventoryUpdated" flag.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:42
Joined
Feb 19, 2013
Messages
13,250
Agree with Minty, storing calculated value such as this can easily go wrong and be be a nightmare to sort out.

With regards your subquery you are creating a cartesian query and you are not aliasing supplies both of which will cause problems

you could try

Code:
UPDATE Supplies SET InStock = (SELECT A.InStock + Orders.Qty
FROM Supplies AS A INNER JOIN Orders ON A.SupplyID = Orders.SupplyID
WHERE Orders.IsComplete = True
AND Orders.InventoryUpdated = False
AND A.SupplyID=Supplies.SupplyID)


or

Code:
UPDATE Supplies SET Instock=Instock+Qty
FROM Supplies INNER JOIN (SELECT SupplyID, Qty FROM Orders WHERE isComplete and not inventoryUpdated) B ON Supplies.SupplyID=B.SupplyID
 

ELiedeker

Member
Local time
Today, 02:42
Joined
Sep 22, 2020
Messages
33
Agree with Minty, storing calculated value such as this can easily go wrong and be be a nightmare to sort out.

With regards your subquery you are creating a cartesian query and you are not aliasing supplies both of which will cause problems

you could try

Code:
UPDATE Supplies SET InStock = (SELECT A.InStock + Orders.Qty
FROM Supplies AS A INNER JOIN Orders ON A.SupplyID = Orders.SupplyID
WHERE Orders.IsComplete = True
AND Orders.InventoryUpdated = False
AND A.SupplyID=Supplies.SupplyID)


or

Code:
UPDATE Supplies SET Instock=Instock+Qty
FROM Supplies INNER JOIN (SELECT SupplyID, Qty FROM Orders WHERE isComplete and not inventoryUpdated) B ON Supplies.SupplyID=B.SupplyID
Thanks so much for the sql. The first query gives me the "updateable query" error, and the second one gives me "Syntax error (missing operator)". I've been getting these errors over and over with every iteration of this query I try (which has been every way of writing it I can think of). At least now I know that this is a feasible operation in Access. Could it have to do with relationships or something else?
 

ELiedeker

Member
Local time
Today, 02:42
Joined
Sep 22, 2020
Messages
33
Thanks so much for the sql. The first query gives me the "updateable query" error, and the second one gives me "Syntax error (missing operator)". I've been getting these errors over and over with every iteration of this query I try (which has been every way of writing it I can think of). At least now I know that this is a feasible operation in Access. Could it have to do with relationships or something else?
Also, there's no syntax error in the second query if I substitute "Update Supplies SET InStock =" with "select". I get the calculated value back no problem.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:42
Joined
Feb 19, 2013
Messages
13,250
written on the hoof so there may be errors.

Suggest from your 'SELECT' version, in the query builder, change to an update query. That should fix any syntax error
 

ELiedeker

Member
Local time
Today, 02:42
Joined
Sep 22, 2020
Messages
33
written on the hoof so there may be errors.

Suggest from your 'SELECT' version, in the query builder, change to an update query. That should fix any syntax error
That fixed me up!! Thanks so much! I'm so much more comfortable with SQL than Access... that never occurred to me. I appreciate your help.
 

Users who are viewing this thread

Top Bottom