Kip Kip - 11 months ago 50
SQL Question

How do I perform update query with subquery in Access?

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 Source

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


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