SQL Access DB 用另一个表的值更新一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/787186/
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
Access DB update one table with value from another
提问by m42
I'm trying to update all records in one table with the values found in another table.
我正在尝试使用在另一个表中找到的值更新一个表中的所有记录。
I've tried many versions of the same basic query and always get the same error message:
我已经尝试了相同基本查询的许多版本，但总是得到相同的错误消息：
Operation must use an updateable query.
操作必须使用可更新的查询。
Any thoughts on why this query won't work in Access DB?
关于为什么此查询在 Access DB 中不起作用的任何想法？
UPDATE inventoryDetails as idet
SET idet.itemDesc =
(
SELECT bomItemDesc
FROM BOM_TEMPLATES as bt
WHERE bt.bomModelNumber = idet.modelNumber
)
also tried this because I realized that since the second table has multiple model number records for each modelnumber - and I only need the first description from the first record found for each model number.
也尝试过这个，因为我意识到因为第二个表有每个型号的多个型号记录 - 我只需要为每个型号找到的第一条记录中的第一个描述。
UPDATE inventoryDetails as idet
SET idet.item_desc =
(
SELECT TOP 1 bomItemDescription
FROM BOM_TEMPLATES as bt
WHERE bt.bomModelNumber = idet.modelNumber
)
...still getting the same error though.
...虽然仍然得到同样的错误。
回答by DJ.
You have to use a join
你必须使用连接
UPDATE inventoryDetails
INNER JOIN BOM_TEMPLATES ON inventoryDetails.modelNumber = BOM_TEMPLATES.bomModelNumber
SET inventoryDetails.itemDesc = [bomItemDesc];
回答by onedaywhen
Any thoughts on why this query won't work in Access DB?
Any thoughts on why this query won't work in Access DB?
The answer is, because ACE/Jet SQL syntax is not SQL-92 compliant (even when in its ANSI-92 Query Mode!).
答案是，因为 ACE/Jet SQL 语法不符合 SQL-92（即使在其 ANSI-92 查询模式下！）。
I'm assuming yours is a scalar subquery. This construct is simply not supported by ACE/Jet.
我假设你的是一个标量子查询。ACE/Jet 根本不支持这种结构。
ACE/Jet has its own quirky and flawed UPDATE..JOINsyntax, flawed because the engine doesn't force the JOINed values to be scalar and it is free to silently use an arbitrary value. It is different again from SQL Server's own UPDATE..JOIN syntax but at least SQL Server supports the Standard scalar subquery as an alternative. ACE/Jet forces you to either learn its quirky non-portable ways or to use an alternative SQL product.
ACE/Jet 有自己古怪和有缺陷的UPDATE..JOIN语法，有缺陷是因为引擎不强制JOINed 值是标量，并且可以自由地静默使用任意值。它再次不同于 SQL Server 自己的 UPDATE..JOIN 语法，但至少 SQL Server 支持标准标量子查询作为替代。ACE/Jet 迫使您要么学习其古怪的非便携方式，要么使用替代 SQL 产品。
Sorry to sound negative: the ACE/Jet engine is a great piece of software but UPDATE syntax is absolutely fundamental and the fact it hasn't been changed since the SQL-92 Standard really show its age.
抱歉，听起来很消极：ACE/Jet 引擎是一款很棒的软件，但 UPDATE 语法绝对是基础，而且自从 SQL-92 标准真正显示其年龄以来，它就没有改变过这一事实。
回答by DCNYAM
try:
尝试：
update idet
SET idet.itemDesc = bt.bomItemDesc
from inventoryDetails as idet
inner join BOM_TEMPLATES as bt
on bt.bomModelNumber = idet.modelNumber
This is how I would write it for SQL server. Hope Access understands the same command.
这就是我为 SQL 服务器编写它的方式。希望 Access 理解相同的命令。
