如何在 Access 中使用子查询执行更新查询? [英] How do I perform update query with subquery in Access?

问题描述

我想将此 SQL 查询移植到 Access 数据库，该查询在 SQL Server、MySQL 和 Oracle 上运行良好.我怎么做?现在它出于某种原因提示我输入 Company_ID.

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.

编辑:我收到提示是因为我忘记先在 VendorRegKeys 中创建 Company_ID 列.现在我收到错误操作必须使用可更新的查询".

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)

<小时>

更新:根据 JuniorFlip 的回答，我发现此方法可行:

---

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

推荐答案

这可能是因为 Company_ID 不是 VendorRegKeys OR Users 中的现有字段.

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

这篇关于如何在 Access 中使用子查询执行更新查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！