Access DB updates one table with a value from another

Question

I am trying to update all records in one table with the values ​​found in another table.

I tried many versions of the same basic query and always got the same error message:

The operation must use an updated query.

Any thoughts on why this query will not 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 contains several model number records for each model number, 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.

Answer 1

You need to use a connection

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

Answer 2

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

The answer is that ACE / Jet SQL syntax is not compatible with SQL-92 (even in ANSI-92 Query Mode!).

I assume yours is a scalar subquery. This design is simply not supported by ACE / Jet.

ACE / Jet has its own dodgy and erroneous UPDATE..JOIN syntax, erroneous because the engine does not force the JOIN ed values ​​to be scalar, and it is free to use an arbitrary value. This again differs from the UPDATE..JOIN SQL Server syntax, but at least SQL Server supports the standard scalar subquery as an alternative. ACE / Jet forces you to either learn your fancy non-portable methods or use an alternative SQL product.

Sorry that the sound is negative: the ACE / Jet engine is great software, but the UPDATE syntax is absolutely fundamental and the fact that it has not been changed since the SQL-92 standard really shows its age.

Answer 3

to 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 an SQL server. Hope Access understands the same command.