如何在Access中使用子查询执行更新查询? [英] How do I perform update query with subquery in Access?
问题描述
我想将此SQL查询(在SQL Server,MySQL和Oracle上运行良好)移植到Access数据库.我怎么做? 现在由于某种原因,它会提示我输入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屋!