CODEWITHSUNDEEP
sqlsql-updatems-access-2013
kaka
kaka

Reputation: 11

Update statement not working properly in Microsoft Access

In microsoft access:

Update Orders
Set TotalPrice  = (SELECT Max(Total)
FROM OrderDetails)

Can anyone please explain me why this doesn't work?

Upvotes: 1

Views: 415

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123484

Your query fails with "Operation must use an updateable query" because the Access Database Engine tends to make queries "not updateable" if they use certain features like SQL aggregation functions (e.g., MAX()), outer joins, etc..

In your particular case the equivalent query for Access would use the DMax() domain aggregate function:

UPDATE Orders SET TotalPrice = DMax("Total", "OrderDetails")

Upvotes: 1

Related Questions