SQL 如何在 Access 中使用子查询执行更新查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1375207/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I perform update query with subquery in Access?
提问by Kip
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.
我想将这个在 SQL Server、MySQL 和 Oracle 上运行良好的 SQL 查询移植到 Access 数据库。我怎么做？ 现在它出于某种原因提示我输入 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".
编辑：我收到提示是因为我忘记先在 VendorRegKeys 中创建 Company_ID 列。现在我收到错误“操作必须使用可更新的查询”。
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:
更新：我发现这可以根据JuniorFlip的回答工作：
UPDATE VendorRegKeys, Users
SET VendorRegKeys.Company_ID = Users.Company_ID
WHERE VendorRegKeys.CreatedBy_ID = Users.User_ID
采纳答案by shahkalpesh
That could be because Company_ID is notan existing field in VendorRegKeys OR Users.
这可能是因为 Company_ID不是VendorRegKeys OR Users 中的现有字段。
EDIT:
编辑：
UPDATE VendorRegKeys
INNER JOIN Users ON Users.User_ID = VendorRegKeys.CreatedBy_ID
SET VendorRegKeys.Company_ID = Users.Company_ID
回答by onedaywhen
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.
直接回答：你不能。即使在其自己的所谓 ANSI-92 查询模式中，Access 数据库引擎 simple 也不支持普通的 SQL-92 标量子查询语法。
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 articlefor some really unsatisfactory workarounds.
您被迫使用它自己的专有语法，该语法不强制执行标量要求，即不安全并且会任意且静默地选择一个值**。此外，超越了简单的结构也不会做的所有工作中，最明显的是在您的子查询（如果你被允许使用一个摆在首位）使用设定功能（MAX，SUM，等） -看到这篇文章的一些真正不满意的解决方法。
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.
抱歉否定，但这确实是基本语法，我不明白为什么 Access 团队还没有开始修复它。这是我不能再认真对待 Access 数据库引擎的无可争议的第一大原因。
To demonstrate the unsafe behavior of the Access proprietary UPDATE..JOIN..Setsyntax
演示 Access 专有UPDATE..JOIN..Set语法的不安全行为
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
在 Access 中执行更新语句时，UI 会警告我们
You are about to update 2 row(s).
您即将更新 2 行。
despite the fact there is only one row in the VendorRegKeystable!
尽管VendorRegKeys表中只有一行！
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 MERGEsyntax behaves this way too).
使用标准 SQL 的标量子查询语法，您会收到错误并且语句将无法执行，这可以说是所需的功能（标准 SQL 的MERGE语法也是如此）。
回答by JuniorFlip
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
