sql - Access DB update one table with value from another

Question

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.

Answer 1

You have to use a join

UPDATE inventoryDetails 
INNER JOIN BOM_TEMPLATES ON inventoryDetails.modelNumber = BOM_TEMPLATES.bomModelNumber 
SET inventoryDetails.itemDesc = [bomItemDesc];