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

try:

update idet
SET idet.itemDesc = bt.bomItemDesc
from inventoryDetails as idet
     inner join BOM_TEMPLATES as bt
          on bt.bomModelNumber = idet.modelNumber

This is how I would write it for SQL server. Hope Access understands the same command.

Answer 2

You have to use a join

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

Answer 3

Any thoughts on why this query won't work in Access DB?

The answer is, because ACE/Jet SQL syntax is not SQL-92 compliant (even when in its ANSI-92 Query Mode!).

I'm assuming yours is a scalar subquery. This construct is simply not supported by ACE/Jet.

ACE/Jet has its own quirky and flawed UPDATE..JOIN syntax, flawed because the engine doesn't force the JOINed values to be scalar and it is free to silently use an arbitrary value. It is different again from SQL Server's own UPDATE..JOIN syntax but at least SQL Server supports the Standard scalar subquery as an alternative. ACE/Jet forces you to either learn its quirky non-portable ways or to use an alternative SQL product.

Sorry to sound negative: the ACE/Jet engine is a great piece of software but UPDATE syntax is absolutely fundamental and the fact it hasn't been changed since the SQL-92 Standard really show its age.