How to fill in missing info in a table - imported Excel into a table, then?

[stingray]

Hi,


I am pretty new to Access, and Have not written SQL for so many years...used to use SQL Server. So maybe it is something simple. When I run the query, Access says, " Operation must use an updateable query."
Does this mean there is an error in SQL?

I copied Product table, and made ProductImport table. I deleted all existing data in it, and imported data from Excel. So these two tables have exact structure. (The number of rows is different.) In the original, 3 pieces of information are missing, and the imported table has them. I don't mind running the Update query 3 times, though I am interested in doing it in one shot.

Here is the query:

Update Product
Set OuterCase = (
Select OuterCase
From ProductImport
Where product.CustomerSKU = productImport.CustomerSKU
)
Where Exists (
Select OuterCase
From ProductImport
Where product.CustomerSKU = productimport.customerSKU
);

At first, I did not think "Where Exists..." was necessary, but it does not work with or without it. What does it do anyway?
Could you please enlighten me? It must be something really simple.

[June7]

Review https://stackoverflow.com/questions/...crosoft-access

[Micron]

I would say that the reason is probably #5 here
http://allenbrowne.com/ser-61.html
The solution might be to create a query that returns what is required by the sub query and join that to the outer query. FWIW, Excel sheets are seldom suitable for Access work, and the right option might be to create a properly designed table(s) to accept the sheet data. Then link to the sheet from Access and use update/append or upsert query to copy sheet data into the Access tables. If you have not studied db normalization already, I'd strongly advise that you do that first.

Alternatively, you can import into Access, but the sheet data will no longer be dynamic inside of Access. You'd still need to copy the un-normalized (?) sheet data into Access tables.

[stingray]

OP here.
I changed direction, and let Access do it for me. It gave me this query:

UPDATE Product INNER JOIN ProductImport ON Product.CustomerSKU = ProductImport.CustomerSKU

SET Product.OuterCase = [ProductImport]![OuterCase],

Product.InnerCase = [ProductImport]![InnerCase],

Product.RetailPrice = [ProductImport]![RetailPrice];

[madpiet]

That's weird syntax. (Well, for me) Normally more like

UPDATE Product
SET
Product.OuterCase = [ProductImport]![OuterCase],

Product.InnerCase = [ProductImport]![InnerCase],

Product.RetailPrice = [ProductImport]![RetailPrice]


WHERE Product.CustomerSKU = ProductImport.CustomerSKU;

[jojowhite]

In Access, you can do it in 2 (i think) ways.

1. Using joins:

UPDATE Product 
INNER JOIN ProductImport 
ON Product.CustomerSKU =  productImport.CustomerSKU 
SET Product.OuterCase = productImport.OuterCase,
Product.InnerCase = ProductImport.InnerCase,
Product.RetailPrice = ProductImport.RetailPrice;

2. Using cross-join

UPDATE Product, productImport 
SET Product.OuterCase = productImport.OuterCase 
Product.InnerCase = ProductImport.InnerCase,
Product.RetailPrice = ProductImport.RetailPrice 
WHERE
Product.CustomerSKU = productImport.CustomerSKU;