How do I perform update query with subquery in Access?

Question

I want to port this SQL query, which works just fine on SQL Server, MySQL, and Oracle, to an Access database. How do I do that? Right now it prompts me for a Company_ID for some reason.

Edit: I was getting the prompt because I forgot to first create the Company_ID column in VendorRegKeys. Now I am getting error "Operation must use an updateable query".

UPDATE VendorRegKeys
   SET Company_ID = (SELECT Users.Company_ID
                     FROM Users
                     WHERE Users.User_ID = VendorRegKeys.CreatedBy_ID)

---

Update: I found this to work based on JuniorFlip's answer:

UPDATE VendorRegKeys, Users
SET VendorRegKeys.Company_ID = Users.Company_ID
WHERE VendorRegKeys.CreatedBy_ID = Users.User_ID

Answer 1

That could be because Company_ID is not an existing field in VendorRegKeys OR Users.

EDIT:

UPDATE VendorRegKeys
INNER JOIN Users ON Users.User_ID = VendorRegKeys.CreatedBy_ID
SET VendorRegKeys.Company_ID = Users.Company_ID

Answer 2

you could try this one

update a
set a.company_id = b.company_id
from vendorRegkeys a, users b
where a.createdby_id = b.user_id

Answer 3

Straight answer: you can't. The Access Database Engine simple does not support the vanilla SQL-92 scalar subquery syntax even when in its own so-called ANSI-92 Query Mode.

You are forced to use its own proprietary syntax which does not enforce the scalar requirement i.e. is unsafe and will pick a value arbitrarily and silently. Further, beyond simple constructs it does not work at all, most notably where your subquery (if you were allowed to use one in the first place) uses an set function (MAX, SUM, etc) -- see this article for some really unsatisfactory workarounds.

Sorry to be negative but this is really basic syntax and I can't understand why the Access team haven't gotten around to fixing it yet. It is the undisputed number one reason why I can't take the Access database engine seriously anymore.