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屋！