Access数据库使用另一个值更新一个表 [英] Access DB update one table with value from another
问题描述
我尝试用一个表中的所有记录更新另一个表中的值。
我尝试过许多版本的相同基本查询,获取相同的错误消息:
操作必须使用可更新的
查询。
关于为什么此查询在Access DB中不起作用的任何想法?
UPDATE inventoryDetails as idet
SET idet.itemDesc =
(
SELECT bomItemDesc
FROM BOM_TEMPLATES as bt
WHERE bt.bomModelNumber = idet.modelNumber
)
也尝试过,因为我意识到,因为第二个表有多个型号号码记录为每个型号 - 只需要从为每个型号编号找到的第一个记录的第一个描述。
UPDATE inventoryDetails as idet
SET idet。 item_desc =
(
SELECT TOP 1 bomItemDescription
FROM BOM_TEMPLATES as bt
WHERE bt.bomModelNumber = idet.modelNumber
)
解决方案
您必须使用加入
更新inventoryDetails
INNER JOIN BOM_TEMPLATES ON inventoryDetails.modelNumber = BOM_TEMPLATES。 bomModelNumber
SET inventoryDetails.itemDesc = [bomItemDesc];
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?
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.
解决方案You have to use a join
UPDATE inventoryDetails INNER JOIN BOM_TEMPLATES ON inventoryDetails.modelNumber = BOM_TEMPLATES.bomModelNumber SET inventoryDetails.itemDesc = [bomItemDesc];
这篇关于Access数据库使用另一个值更新一个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!