Access DB 用另一个表的值更新一个表 [英] Access DB update one table with value from another
问题描述
我正在尝试使用在另一个表中找到的值更新一个表中的所有记录.
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.
有没有想过为什么这个查询在 Access DB 中不起作用?
Any thoughts on why this query won't work in 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.
推荐答案
你必须使用连接
UPDATE inventoryDetails
INNER JOIN BOM_TEMPLATES ON inventoryDetails.modelNumber = BOM_TEMPLATES.bomModelNumber
SET inventoryDetails.itemDesc = [bomItemDesc];
这篇关于Access DB 用另一个表的值更新一个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!