Access数据库使用另一个值更新一个表 [英] Access DB update one table with value from another

问题描述

我尝试用一​​个表中的所有记录更新另一个表中的值。

我尝试过许多版本的相同基本查询，获取相同的错误消息：

操作必须使用可更新的
查询。

关于为什么此查询在Access DB中不起作用的任何想法？

  UPDATE inventoryDe​​tails as idet 
 SET idet.itemDesc = 
（
 SELECT bomItemDesc 
 FROM BOM_TEMPLATES as bt 
 WHERE bt.bomModelNumber = idet.modelNumber 
）
  

也尝试过，因为我意识到，因为第二个表有多个型号号码记录为每个型号 - 只需要从为每个型号编号找到的第一个记录的第一个描述。

  UPDATE inventoryDe​​tails as idet 
 SET idet。 item_desc = 
（
 SELECT TOP 1 bomItemDescription 
 FROM BOM_TEMPLATES as bt 
 WHERE bt.bomModelNumber = idet.modelNumber 
）
  
解决方案
 
 
 
 
您必须使用加入
 更新inventoryDe​​tails 
 INNER JOIN BOM_TEMPLATES ON inventoryDe​​tails.modelNumber = BOM_TEMPLATES。 bomModelNumber 
 SET inventoryDe​​tails.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屋！