如何在Access中使用子查询执行更新查询？

Question

我想将这个在SQL Server，MySQL和Oracle上都能正常运行的SQL查询转移到Access数据库。我该怎么做？现在它总是提示我输入Company_ID。

编辑：我之前忘记在VendorRegKeys中先创建Company_ID列，所以才一直出现提示。现在我收到错误信息：“操作必须使用可更新的查询”。

UPDATE VendorRegKeys
   SET Company_ID = (SELECT Users.Company_ID
                     FROM Users
                     WHERE Users.User_ID = VendorRegKeys.CreatedBy_ID)

---

更新: 我发现这个方法是有效的，基于 JuniorFlip 的回答：

UPDATE VendorRegKeys, Users
SET VendorRegKeys.Company_ID = Users.Company_ID
WHERE VendorRegKeys.CreatedBy_ID = Users.User_ID

- Kip

---

注意：如果“Users”是一个查询而不是一个可更新的表，则结果为“操作必须使用可更新的查询”。 - Corey Trager

Answer 1

直截了当的回答是：你无法这样做。即使在其所谓的ANSI-92查询模式下，Access数据库引擎也不支持原始SQL-92标量子查询语法。

你被迫使用它自己的专有语法，这种语法不强制执行标量要求，即不安全，并会任意地静默选择一个值。而且，在除了简单构造之外，它根本不起作用，尤其是如果您的子查询（如果您首先允许使用子查询）使用集合函数（例如MAX、SUM等）时--请参见此文章以获取一些真正不令人满意的解决方法。

很抱歉要这么消极，但这是非常基本的语法，我不明白为什么Access团队还没有解决它。这是我无法再认真对待Access数据库引擎的毋庸置疑的头号原因。

为了演示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;

在 Access 中执行更新语句时，应用程序警告：

您将要更新2行数据。

尽管在 VendorRegKeys 表中只有一行数据！

实际情况是我们只能更新单个行中的某个值，而无法可靠地预测哪个值会被更新。

使用标准 SQL 的标量子查询语法，您将会收到一个错误并且该语句将无法执行，这可能是理想的功能（标准 SQL 的 MERGE 语法也是如此）。

- onedaywhen

---

感谢提供的所有信息。我从未认真考虑过Access，但管理层说我们必须支持它。:-/ 至少在这种情况下有一种可行的替代语法。 - Kip

与Timemachine的链接：https://web.archive.org/web/20131004232823/http://support.microsoft.com/kb/116142 - Summer-Time

Answer 2

可能是因为Company_ID在VendorRegKeys或Users中不是一个已存在的字段。

编辑：

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

- shahkalpesh

---

抱歉，VendorRegKeys 中不存在 company_ID。但是在添加后，我现在得到了“操作必须使用可更新的查询”错误。 - Kip

没有看到你已经编辑了一个可行的查询...接受你的答案，因为它确实有效。谢谢！ - Kip

Answer 3

你可以尝试这个。

update a
set a.company_id = b.company_id
from vendorRegkeys a, users b
where a.createdby_id = b.user_id

- JuniorFlip

---

嗯... 这给出了一个错误：查询表达式中缺少运算符，即 'b.company_id from vendorRegkeys a'。 - Kip

我用稍微修改过的版本成功运行了它，已在问题中更新。谢谢！ - Kip