Access 2016 - Simple update query gives "Operation must use an updateable query" Error

Question

I'm trying to transfer the color field value from the dress table to the purchase table as I'm changing my DB's structure slightly.

Here's the query, it's simple enough but I keep getting the error.

UPDATE purchase
SET purchase.color = (SELECT dress.color
                      FROM dress INNER JOIN purchase
                      ON dress.ID = purchase.dressID);

I've also tried without JOIN and putting the condition in the WHERE clause but I get the same error.

UPDATE purchase
SET purchase.color = (SELECT dress.color
                      FROM dress, purchase
                      WHERE dress.ID = purchase.dressID);

I've also read that this error is sometimes due to the file being "read only" or other issues with permission, I've tried everything but nothing seems to work.

Edit: also tried this but I get "missing operator" error

UPDATE purchase
SET purchase.color = dress.color
FROM dress INNER JOIN purchase ON dress.ID = purchase.dressID;

Possible duplicate of [SQL Update woes in MS Access - Operation must use an updateable query](https://stackoverflow.com/questions/537161/sql-update-woes-in-ms-access-operation-must-use-an-updateable-query) — Ken White, Nov 07 '17 at 02:47
Thank you, I was browsing through some threads but this one had the answer, a really weird one at that. I will edit with an answer below. Thanks again! — Daniel, Nov 07 '17 at 02:54

score 1 · Answer 1 · answered Nov 07 '17 at 03:21

1

I think you really want a simple, correlated subquery:

UPDATE purchase
    SET color = (SELECT d.color
                 FROM dress as d 
                 WHERE d.ID = purchase.dressID
                );

If there might be duplicates in the subquery, try TOP 1:

UPDATE purchase
    SET color = (SELECT TOP 1 d.color
                 FROM dress as d 
                 WHERE d.ID = purchase.dressID
                );

answered Nov 07 '17 at 03:21

Gordon Linoff

1,242,037
58
646
786

Hi, thanks for the response but I actually fixed the problem, if you're curious as to how check my answer above. – Daniel Nov 07 '17 at 04:48
1

@Daniel . . . Your supposed solution does not do what you expect. I would suggest you use the versions in this answer. – Gordon Linoff Nov 07 '17 at 13:02

score 0 · Answer 2 · answered Nov 07 '17 at 02:55

0

I found an answer thanks to the link by Ken White.

This query with the weird keyword 'DISTINCTROW' fixed the problem.

UPDATE DISTINCTROW purchase
INNER JOIN dress ON purchase.dressID = dress.ID
SET purchase.color = dress.color;

answered Nov 07 '17 at 02:55

Daniel

95
3
11

1

Have you tried this query without DISTINCTROW? Because the JOIN syntax in your question (3rd try) was wrong - it must be `UPDATE x JOIN y SET x...`, not `UPDATE x SET x... FROM x JOIN y` – Andre Nov 07 '17 at 08:04

Access 2016 - Simple update query gives "Operation must use an updateable query" Error

2 Answers2