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

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.

---

To demonstrate the unsafe behavior of the Access proprietary UPDATE..JOIN..Set syntax

CREATE TABLE Users
( 
  User_ID CHAR( 3 ) NOT NULL,
  Company_ID CHAR( 4 ) NOT NULL,
  UNIQUE ( Company_ID, User_ID ) );

CREATE TABLE VendorRegKeys
  CreatedBy_ID CHAR( 3 ) NOT NULL UNIQUE,
  Company_ID CHAR( 4 ) );

INSERT INTO Users VALUES ( 'Kip', 'MSFT' );
INSERT INTO Users VALUES ( 'Kip', 'AAPL' );

INSERT INTO VendorRegKeys VALUES ( 'Kip', NULL );

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

When executing the update statement within Access, the UI warns we

You are about to update 2 row(s).

despite the fact there is only one row in the VendorRegKeys table!

What happens in practise is just one of the values we will used to update the column in that single row, without a reliable way of predicting which it will be.

With Standard SQL's scalar subquery syntax, you would get an error and the statement would fail to execute, which is arguably the desired functionality (Standard SQL's MERGE syntax behaves this way too).

Answer 2

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